Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    12

    Lightbulb Unanswered: Average and Total Calculation in Single Query

    INTRODUCTION TO DATABASE TABLE BEING USED -

    I am working on a “Stock Market Prices” based Database Table. My table has got the data for the following FIELDS –
    ID
    SYMBOL
    OPEN
    HIGH
    LOW
    CLOSE
    VOLUME
    VOLUME CHANGE
    VOLUME CHANGE %
    OPEN_INT
    SECTOR
    TIMESTAMP

    New data gets added to the table daily “Monday to Friday”, based on the stock market price changes for that day. The current requirement is based on the “VOLUME “field, which shows the volume traded for a particular stock on daily basis.

    REQUIREMENT –
    To get the Average and Total Volume for last 10,15 and 30 Days respectively.

    METHOD USED CURRENTLY -
    I created these 9 SEPARATE QUERIES in order to get my desired results –
    First I have created these 3 queries to take out the most recent last 10,15 and 30 dates from the current table.
    qryLast10DaysStored
    qryLast15DaysStored
    qryLast30DaysStored

    Then I have created these 3 queries for getting the respective AVERAGES
    qrySymbolAvgVolume10Days
    qrySymbolAvgVolume15Days
    qrySymbolAvgVolume30Days

    And then I have created these 3 queries for getting the respective TOTALS
    qrySymbolTotalVolume10Days
    qrySymbolTotalVolume15Days
    qrySymbolTotalVolume30Days

    PROBLEM BEING FACED WITH CURRENT METHOD -

    Now, my problem is that I have ended up having these so many different queries, whereas I wanted to get the output into One Single Query, as shown in the Snapshot of the Excel Sheet.
    http://i49.tinypic.com/256tgcp.png

    SOLUTION NEEDED -
    Is there some way by which I can get these required fields into ONE SINGLE QUERY, so that I do not have to look into multiple places for the required fields? Can someone please tell me how to get all these separate queries into one -
    A) Either by taking out or moving the results from these separate individual queries to one.
    B) Or by making a new query which calculates all these fields within itself, so that these separate individual queries are no longer needed. This would be a better solution I think.

    One Clarification about Dates – Some friend might think why I used the method of using Top 10,15 and 30 for getting the last 10,15 and 30 Date Values. Why not I just used the PC Date for getting these values? Or used something like - ("VOLUME","tbl-B", "TimeStamp BETWEEN Date() - 10 AND Date()")
    The answer is that I require my query to "Read" the date from the "TIMESTAMP" Field, and then perform its calculations accordingly for LAST / MOST RECENT "10 days, 15 days, 30 days” FOR WHICH THE DATA IS AVAILABLE IN THE TABLE, WITHOUT BOTHERING WHAT THE CURRENT DATE IS. It should not depend upon the current date in any way.

    If there is any better method or more efficient way to create these queries, then please enlighten.

    Thanks a lot

    PS : I have attached a SAMPLE database which has the data for 6 months, from 1 Nov 2011 to 30 April 2012 and it has also got the above mentioned queries in it.
    Attached Thumbnails Attached Thumbnails Required Fields in Single Query.png  
    Attached Files Attached Files

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Unfortunately, I can't open the zipped file. My problem, not yours.

    In any event, I don't see why you just don't do this.

    Use a combobox to select whether you want 10 days of data, 15, or 30. (Of course you can select any number at all.) Change the TOP number per the value of the combobox - you only need one query for that; you only have to pass the value from the combobox to the query.

    Make the query qryLastXDaysStored as the recordset in the other two queries - you only need one query each for that, as well - so you don't have to keep re-selecting the same records. If you do this, you don't run the qryLastXDaysStored query independently. You only run the average and total queries, which automatically pass the top value to, and run, the qryLastXDaysStored query.

    By the way, if your TIMESTAMP is a true timestamp, you will not get a true 10-day recordset, as the timestamp value changes every second. Is it really a DATESTAMP?

    Sam

    PS Some of my comments may be off the wall as a result of my not being able to open the zip file and read the various objects. My apologies for any misunderstandings.

  3. #3
    Join Date
    Oct 2012
    Posts
    12
    Quote Originally Posted by Sam Landy View Post
    Unfortunately, I can't open the zipped file. My problem, not yours.

    In any event, I don't see why you just don't do this.

    Use a combobox to select whether you want 10 days of data, 15, or 30. (Of course you can select any number at all.) Change the TOP number per the value of the combobox - you only need one query for that; you only have to pass the value from the combobox to the query.

    Make the query qryLastXDaysStored as the recordset in the other two queries - you only need one query each for that, as well - so you don't have to keep re-selecting the same records. If you do this, you don't run the qryLastXDaysStored query independently. You only run the average and total queries, which automatically pass the top value to, and run, the qryLastXDaysStored query.

    By the way, if your TIMESTAMP is a true timestamp, you will not get a true 10-day recordset, as the timestamp value changes every second. Is it really a DATESTAMP?

    Sam

    PS Some of my comments may be off the wall as a result of my not being able to open the zip file and read the various objects. My apologies for any misunderstandings.
    Thank you so much for your reply Sam. No need to apologize for anything. You will understand the situation more clearly if you are able to open the database because I have already made the required queries. Just some improvement needs to be done into it.

    Could you please let me know what should I do in order to let you open the database ? If I upload it without being ziped then will you be able to see it ?

    Thanks

  4. #4
    Join Date
    Oct 2012
    Posts
    12
    Sam Landy, I have uploaded the database directly at this link, without zipping it first - LimeLinx - Home - Free File Hosting and Sharing - Audio, Video, Image

    I have also updated 2 solutions provided by other friends in it.

    I have implemented a solution provided by a friend HansUp on the Stack Overflow forum. His method has helped me to reduce 3 queries by combining the Average and Total into one. Its funny that I did not get this idea earlier.

    Please see these 3 new queries -

    qry10DayVolumes
    qry15DayVolumes
    qry30DayVolumes

    Also have a look at the query named - Johnc .This is the solution provide by a friend Access_Johnc on the utteraccess forum. He has combined my original 9 queries into a single query by using Inner Join method. Now I am trying to make it even more efficient.

    Thanks a lot for your help.

  5. #5
    Join Date
    Oct 2012
    Posts
    12

    Lightbulb

    Now here comes even better method ! Just one query to get all the required fields, no more messing around with multiple queries -


    select
    Symbol,
    sum([10DayTotalVol]) as 10DayTotalV,
    sum([10DayAvgVol]) as 10DayAvgV,
    sum([15DayTotalVol]) as 15DayTotalV,
    sum([15DayAvgVol]) as 15DayAvgV,
    sum([30DayTotalVol]) as 30DayTotalV,
    sum([30DayAvgVol]) as 30DayAvgV

    from (

    select
    Symbol,
    sum(volume) as 10DayTotalVol, avg(volume) as 10DayAvgVol,
    0 as 15DayTotalVol, 0 as 15DayAvgVol,
    0 as 30DayTotalVol, 0 as 30DayAvgVol
    from
    [tbl-b]
    where
    timestamp >= (select min(ts) from (select distinct top 10 timestamp as ts from [tbl-b] order by timestamp desc ))
    group by
    Symbol

    UNION

    select
    Symbol,
    0, 0,
    sum(volume), avg(volume),
    0, 0
    from
    [tbl-b]
    where
    timestamp >= (select min(ts) from (select distinct top 15 timestamp as ts from [tbl-b] order by timestamp desc ))
    group by
    Symbol

    UNION

    select
    Symbol,
    0, 0,
    0, 0,
    sum(volume), avg(volume)
    from
    [tbl-b]
    where
    timestamp >= (select min(ts) from (select distinct top 30 timestamp as ts from [tbl-b] order by timestamp desc ))
    group by
    Symbol
    ) s

    group by
    Symbol
    ExactaBox @stackoverflow Rocks ! :thumbup:

    Thanks
    Last edited by 44hjk44; 10-25-12 at 12:49.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Glad I was of some help; looks like you have some very good friends.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •