Average last 30 days for each day in the quarter

  • Thread starter Thread starter jgroom
  • Start date Start date
J

jgroom

I have an access database with a table of info for the past year.
236 records for each day x 365 days.

What I need is an access query that will give me an average for each
day in the quarter, so if I pick quarter 3, I need each day from 7/1
to 9/30, 92 records total.
Each of these records needs an average of the past 30 days.
So an average number from all of the records between 7/1 to 30 days
prior to 7/1.
7/2 would be an average between 7/2 and 6/2.
7/3 would be an average between 7/3 and 6/3.
and so on for each day of the quarter.

I have made a query that will give me the 30 day average for a
specific day.

SELECT QryDate, Avg(Num1) AS AvgNum1, Avg(Num2) AS AvgNum2, avg(Num3)
AS AvgNum3
FROM NumData
WHERE DateStamp Between QryDate And DateAdd("d",-30,QryDate);

So when I run it, it asks for QryDate, if i put in 7/1/2008, it gives
me the average of 30 days prior to 7/1.
This works fine.

I have another query that gives the dates in a quarter.
SELECT Format(DateStamp,"mm/dd/yy") AS Dates
FROM NumData
WHERE Format(DateStamp,"q")=QryQuarter
GROUP BY Format(DateStamp,"mm/dd/yy");
I put in 3 for QryQuarter and it gives me all of the dates for the 3rd
quarter.

I then have a 3rd query that I thought would ask for the quarter and
run the 30 day average for each date in the quarter.
SELECT [30 day average].QryDate, AvgNum1, AvgNum2, AvgNum3
FROM [30 day average]
WHERE Format([30 day average].QryDate,"mm/dd/yyyy") in (Select Dates
from DatesInQuarter);

When I run this 3rd query, it asks for QryDate and QryQuarter both.
If I type in 7/1/2008 and quarter 3, it just shows me the 1 record for
7/1.

If I leave qrydate blank and put in 3 for qryquarter it has no
records.

I need it in a single query because I have a report that runs the
query.
It was temporarly put together with a temp table that held the 30 day
average for each day.
I don't want to run a vbscript that puts these dates in a temporary
table each quarter and the user shouldn't have to do that.
It needs to be all dynamic.

Does anyone know how I can do this without making a temporary table
and without looping within vbscript/vba?

I hope this all makes sense!
Thanks for any help!!
 
Try this, putting in your number field name --
SELECT Format([DateStamp],"yyyy q") AS Expr1,
Avg([NumData].[YourNumberField]) AS AvgOfNUM
FROM [NumData]
GROUP BY Format([DateStamp],"yyyy q");
 
Try this, putting in your number field name --
SELECT Format([DateStamp],"yyyy q") AS Expr1,
Avg([NumData].[YourNumberField]) AS AvgOfNUM
FROM [NumData]
GROUP BY Format([DateStamp],"yyyy q");
--
KARL DEWEY
Build a little - Test a little

- Show quoted text -
Click to expand...

Sorry if I didn't explain better, but this gives me the average for
the whole quarter as 1 record.
I need each day of the quarter as records.
and I only need to view 1 quarter, not all of them.
so for q3 2008, i'll have 92 records, 1 for each day of the quarter.
each 1 of those records is a 30 day average of the previous 30 days.
Thanks for the reply!!
 
I think I got all of the field names right.
How about:

SELECT T1.QryDate,
Avg(T2.Num1) as AvgNum1,
Avg(T2.Num2) as AvgNum2,
Avg(T2.Num3) as AvgNum3
FROM (SELECT DISTINCT DateStamp as QryDate
FROM NumData
WHERE Format([DateStamp], "q") = QryQuarter) as T1
LEFT JOIN NumData
ON T2.DateStamp >= DateAdd("d", -30, T1.QryDate)
AND T2.DateStamp <= T1.QryDate
GROUP BY T1.QryDate

The down-side of a non-equi join is that you cannot write this in the query
grid, you have to do it in the SQL view. Basically what this says is give me
all the distinct dates in QryQuarter, then join that to table [NumData] so
that every record in T1 is linked to each record in NumData where the
DateStamp is within T1.QryDate -30 and T1.QryDate. Finally, it groups by
T1.QryDate and averages the values that are related to it.

But this query doesn't account for the year, so it will include records from
NumData for 3rd quarter of each of 2006, 2007, and 2008, if you have those
years in your table. If you want to restrict the report to dates from a
particular quarter of this year, then you need to add an item to the WHERE
clause of the subquery.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



[email protected] said:
I have an access database with a table of info for the past year.
236 records for each day x 365 days.

What I need is an access query that will give me an average for each
day in the quarter, so if I pick quarter 3, I need each day from 7/1
to 9/30, 92 records total.
Each of these records needs an average of the past 30 days.
So an average number from all of the records between 7/1 to 30 days
prior to 7/1.
7/2 would be an average between 7/2 and 6/2.
7/3 would be an average between 7/3 and 6/3.
and so on for each day of the quarter.

I have made a query that will give me the 30 day average for a
specific day.

SELECT QryDate, Avg(Num1) AS AvgNum1, Avg(Num2) AS AvgNum2, avg(Num3)
AS AvgNum3
FROM NumData
WHERE DateStamp Between QryDate And DateAdd("d",-30,QryDate);

So when I run it, it asks for QryDate, if i put in 7/1/2008, it gives
me the average of 30 days prior to 7/1.
This works fine.

I have another query that gives the dates in a quarter.
SELECT Format(DateStamp,"mm/dd/yy") AS Dates
FROM NumData
WHERE Format(DateStamp,"q")=QryQuarter
GROUP BY Format(DateStamp,"mm/dd/yy");
I put in 3 for QryQuarter and it gives me all of the dates for the 3rd
quarter.

I then have a 3rd query that I thought would ask for the quarter and
run the 30 day average for each date in the quarter.
SELECT [30 day average].QryDate, AvgNum1, AvgNum2, AvgNum3
FROM [30 day average]
WHERE Format([30 day average].QryDate,"mm/dd/yyyy") in (Select Dates
from DatesInQuarter);

When I run this 3rd query, it asks for QryDate and QryQuarter both.
If I type in 7/1/2008 and quarter 3, it just shows me the 1 record for
7/1.

If I leave qrydate blank and put in 3 for qryquarter it has no
records.

I need it in a single query because I have a report that runs the
query.
It was temporarly put together with a temp table that held the 30 day
average for each day.
I don't want to run a vbscript that puts these dates in a temporary
table each quarter and the user shouldn't have to do that.
It needs to be all dynamic.

Does anyone know how I can do this without making a temporary table
and without looping within vbscript/vba?

I hope this all makes sense!
Thanks for any help!!
Click to expand...
 
I think I got all of the field names right.
How about:

SELECT T1.QryDate,
            Avg(T2.Num1) as AvgNum1,
            Avg(T2.Num2) as AvgNum2,
            Avg(T2.Num3) as AvgNum3
FROM (SELECT DISTINCT DateStamp as QryDate
           FROM NumData
           WHERE Format([DateStamp], "q") = QryQuarter) as T1
LEFT JOIN NumData
ON T2.DateStamp >= DateAdd("d", -30, T1.QryDate)
AND T2.DateStamp <= T1.QryDate
GROUP BY T1.QryDate

The down-side of a non-equi join is that you cannot write this in the query
grid, you have to do it in the SQL view.  Basically what this says is give me
all the distinct dates in QryQuarter, then join that to table [NumData] so
that every record in T1 is linked to each record in NumData where the
DateStamp is within T1.QryDate -30 and T1.QryDate.  Finally, it groups by
T1.QryDate and averages the values that are related to it.

But this query doesn't account for the year, so it will include records from
NumData for 3rd quarter of each of 2006, 2007, and 2008, if you have those
years in your table.  If you want to restrict the report to dates from a
particular quarter of this year, then you need to add an item to the WHERE
clause of the subquery.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.


- Show quoted text -
Click to expand...

I do believe this is exactly what I needed!!
From running the 30 day individual queries on each day and comparing
them to the data provided by your query, they appear to match!
There is only 1 year's worth of data in this table, it gets purged
every year.

I pretty much only write in SQL view, but I was having problems
figuring this out.
I thought about a join but I wasn't sure how I would get it all to
work with the 30 day average.
1 slight thing that access barked about was the T2 not being aliased.
so I just updated LEFT JOIN NumData as T2. pasted is the final query.


SELECT T1.DateStamp, Avg(T2.Num1) AS AvgNum1, Avg(T2.Num2) AS AvgNum2,
Avg(T2.Num3) AS AvgNum3
FROM (SELECT DISTINCT DateStamp
FROM NumData
WHERE Format([DateStamp], "q") = QryQuarter) AS T1 LEFT
JOIN NumData AS T2 ON (T2.DateStamp >= DateAdd("d", -30,
T1.DateStamp)) AND (T2.DateStamp <= T1.DateStamp)
GROUP BY T1.DateStamp;

Thanks for all of your help!!!
 
Try this --
SELECT [DateStamp], Avg([NumData].[YourNumberField]) AS AvgOfNUM
FROM [NumData]
GROUP BY [DateStamp]
HAVING Format([DateStamp],"yyyy q") = [Enter Year QTR as 2006 3];

--
KARL DEWEY
Build a little - Test a little


[email protected] said:
Try this, putting in your number field name --
SELECT Format([DateStamp],"yyyy q") AS Expr1,
Avg([NumData].[YourNumberField]) AS AvgOfNUM
FROM [NumData]
GROUP BY Format([DateStamp],"yyyy q");
--
KARL DEWEY
Build a little - Test a little

- Show quoted text -
Click to expand...

Sorry if I didn't explain better, but this gives me the average for
the whole quarter as 1 record.
I need each day of the quarter as records.
and I only need to view 1 quarter, not all of them.
so for q3 2008, i'll have 92 records, 1 for each day of the quarter.
each 1 of those records is a 30 day average of the previous 30 days.
Thanks for the reply!!
Click to expand...
 
Try this --
SELECT [DateStamp], Avg([NumData].[YourNumberField]) AS AvgOfNUM
FROM [NumData]
GROUP BY [DateStamp]
HAVING Format([DateStamp],"yyyy q") = [Enter Year QTR as 2006 3];

--
KARL DEWEY
Build a little - Test a little



[email protected] said:
Try this, putting in your number field name --
SELECT Format([DateStamp],"yyyy q") AS Expr1,
Avg([NumData].[YourNumberField]) AS AvgOfNUM
FROM [NumData]
GROUP BY Format([DateStamp],"yyyy q");
Click to expand...
Click to expand...
Sorry if I didn't explain better, but this gives me the average for
the whole quarter as 1 record.
I need each day of the quarter as records.
and I only need to view 1 quarter, not all of them.
so for q3 2008, i'll have 92 records, 1 for each day of the quarter.
each 1 of those records is a 30 day average of the previous 30 days.
Thanks for the reply!!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Sorry, this won't work for me either.
Dale was kind enough to reply with a query that works.

Thanks for your time and effort! I greatly appriciate it!
 
"purging [your data] every year" is a spreadsheetly way of handling data.

Are you saying you don't need or care about last year's data? There are
other ways of displaying ONLY the current year's data while maintaining data
over the past many years. This approach allows for year-to-year
(period-to-period) comparison.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I think I got all of the field names right.
How about:

SELECT T1.QryDate,
Avg(T2.Num1) as AvgNum1,
Avg(T2.Num2) as AvgNum2,
Avg(T2.Num3) as AvgNum3
FROM (SELECT DISTINCT DateStamp as QryDate
FROM NumData
WHERE Format([DateStamp], "q") = QryQuarter) as T1
LEFT JOIN NumData
ON T2.DateStamp >= DateAdd("d", -30, T1.QryDate)
AND T2.DateStamp <= T1.QryDate
GROUP BY T1.QryDate

The down-side of a non-equi join is that you cannot write this in the
query
grid, you have to do it in the SQL view. Basically what this says is give
me
all the distinct dates in QryQuarter, then join that to table [NumData] so
that every record in T1 is linked to each record in NumData where the
DateStamp is within T1.QryDate -30 and T1.QryDate. Finally, it groups by
T1.QryDate and averages the values that are related to it.

But this query doesn't account for the year, so it will include records
from
NumData for 3rd quarter of each of 2006, 2007, and 2008, if you have those
years in your table. If you want to restrict the report to dates from a
particular quarter of this year, then you need to add an item to the WHERE
clause of the subquery.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.


- Show quoted text -
Click to expand...

I do believe this is exactly what I needed!!
From running the 30 day individual queries on each day and comparing
them to the data provided by your query, they appear to match!
There is only 1 year's worth of data in this table, it gets purged
every year.

I pretty much only write in SQL view, but I was having problems
figuring this out.
I thought about a join but I wasn't sure how I would get it all to
work with the 30 day average.
1 slight thing that access barked about was the T2 not being aliased.
so I just updated LEFT JOIN NumData as T2. pasted is the final query.


SELECT T1.DateStamp, Avg(T2.Num1) AS AvgNum1, Avg(T2.Num2) AS AvgNum2,
Avg(T2.Num3) AS AvgNum3
FROM (SELECT DISTINCT DateStamp
FROM NumData
WHERE Format([DateStamp], "q") = QryQuarter) AS T1 LEFT
JOIN NumData AS T2 ON (T2.DateStamp >= DateAdd("d", -30,
T1.DateStamp)) AND (T2.DateStamp <= T1.DateStamp)
GROUP BY T1.DateStamp;

Thanks for all of your help!!!
 
Back
Top