MS access: average and total calculation in one request

Question

INTRODUCTION TO THE DATABASE TABLE -

I am working on a table on the stock market prices database. My table has data for the following fields -

ID
SYMBOL
OPEN
TALL
Low
Close
VOLUME
VOLUME CHANGE
VOLUME CHANGE%
OPEN_INT
SECTOR
TIMESTAMP

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

REQUIREMENT - To get the average and total volume for the last 10.15 and 30 days, respectively.

METHOD USED DURING - I created these 9 SEPARATE QUESTIONS to get the desired results - First I created these 3 queries to derive the last last 10.15 and 30 dates from the current table:

qryLast10DaysStored
qryLast15DaysStored
qryLast30DaysStored

Then I created these 3 queries to get the corresponding MEANS:

qrySymbolAvgVolume10Days
qrySymbolAvgVolume15Days
qrySymbolAvgVolume30Days

And then I created these 3 queries to get the corresponding TOTALS:

qrySymbolTotalVolume10Days
qrySymbolTotalVolume15Days
qrySymbolTotalVolume30Days

PROBLEM RELATED TO THE CURRENT METHOD - Now my problem is that I had so many different queries, while I wanted to get the output in One Single Query , as shown in "Excel Sheet Shot":

http://i49.tinypic.com/256tgcp.png

NECESSARY DECISIONS - Is there a way by which I can get these required fields in ONE SINGLE TIME, so that I do not need to look for several places for the required fields? Can someone please tell me how to get all these separate requests in one -
A) Either selecting or moving the results from these individual individual queries to one.
B) Or by creating a new query that calculates all of these fields within itself, so that these individual individual queries are no longer needed. That would be the best solution, I think.

One Date Clarification - Some friend might think why I used the Top 10.15 and 30 method to get the last 10.15 and 30 date values. Why didn’t I use the PC date to get these values? Or used something like -

 ("VOLUME","tbl-B", "TimeStamp BETWEEN Date() - 10 AND Date()") 

The answer is that I need my request to “Read” the date from the “TIMESTAMP” field, and then perform its calculations, respectively, for LAST / MOST RECENT “10 days, 15 days, 30 days” FOR WHICH DATA IS AVAILABLE IN THE TABLE, WITHOUT SECURITY WHAT IS THIS DATE. It should not depend on the current date.

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

Answer 1

Access does not support most modern SQL syntax and suggestions, so it’s a bit hacky, but it works and works quickly on your small sample. You basically run 3 queries, but Union proposals allow you to combine into one:

 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 

Answer 2

You have separate queries to calculate 10DayTotalVolume and 10DayAvgVolume . I suspect that you can calculate as in one query, qry10DayVolumes.

 SELECT b.SYMBOL, Sum(b.VOLUME) AS 10DayTotalVolume, Avg(b.VOLUME) AS 10DayAvgVolume FROM [tbl-B] AS b INNER JOIN qryLast10DaysStored AS q ON b.TIMESTAMP = q.TIMESTAMP GROUP BY b.SYMBOL; 

However, this makes me wonder if 10DayAvgVolume be anything other than 10DayTotalVolume / 10

Similar considerations apply to the values ​​of 15 and 30 days.

Ultimately, I think you want something based on a starting point like this:

 SELECT q10.SYMBOL, q10.[10DayTotalVolume], q10.[10DayAvgVolume], q15.[15DayTotalVolume], q15.[15DayAvgVolume], q30.[30DayTotalVolume], q30.[30DayAvgVolume] FROM (qry10DayVolumes AS q10 INNER JOIN qry15DayVolumes AS q15 ON q10.SYMBOL = q15.SYMBOL) INNER JOIN qry30DayVolumes AS q30 ON q10.SYMBOL = q30.SYMBOL; 

It is assumed that you created qry15DayVolumes and qry30DayVolumes according to the approach I proposed for qry10DayVolumes .

If you want to reduce the number of queries, you can use subqueries for each of the qry??DayVolumes stored queries, but first try to do this to make sure the logic is correct.

This second query may cause a problem due to field names that begin with numbers. Fix these names in square brackets or rename them to qry10DayVolumes , qry15DayVolumes and qry30DayVolumes , using aliases starting with letters instead of numbers.

I tested the query, as described above, with "2nd Upload.mdb" uploaded by you, and it ran without errors from Access 2007. Here is the first row of the result set from this query:

 SYMBOL 10DayTotalVolume 10DayAvgVolume 15DayTotalVolume 15DayAvgVolume 30DayTotalVolume 30DayAvgVolume ACC-1 42909 4290.9 54892 3659.46666666667 89669 2988.96666666667