Follow-up: Dynamic 90 Day Running Average

[Guest]

That works beautifly - almost. It will give me the 90 day average for today.
Very nice, thank you.

Is there a way to generate a 90 day average for each day in the database?

PS - when I started writing this "Frankenstein," it was just designed to
keep attendance, so the field names weren't that important. (Poor form, I
know. But at the time, I had never used Access before [old DBMS programmer
of FoxPro and dBase].) I want to change it, and will take the time to do
that here soon...or write the whole thing in VB.

Thanks again.

 

[Guest]

Doom,

Something like the following should work. Note that I actually subtracted
90 days from the the date field in your table, so this assumes that you want
90 continuous days. If you want 90 weekdays this becomes a little more
difficult.

Select T1.DateField, AVG(T2.SomeOtherField) as 90DayMovingAverage
FROM yourTable T1, yourTable T2
WHERE T2.DateField BETWEEN DateAdd("d", -90, T1.DateField) AND T1.DateField
GROUP BY T1.DateField

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

That works beautifly - almost. It will give me the 90 day average for today.
Very nice, thank you.

Is there a way to generate a 90 day average for each day in the database?

PS - when I started writing this "Frankenstein," it was just designed to
keep attendance, so the field names weren't that important. (Poor form, I
know. But at the time, I had never used Access before [old DBMS programmer
of FoxPro and dBase].) I want to change it, and will take the time to do
that here soon...or write the whole thing in VB.

Thanks again.

Let start with the fact that
DateSerial(Year([Date]),Month([Date])-3,Day([Date]))

Is not a good way of getting the start date

1/1/2007 will return -2 for the month

So, you should have used the DateAdd function to subtruct 3 month's from the
date
DateAdd("m",-3,[Date])

***************************
But for your case, if you want the number of entries in the last three
month's try

Select Count(*) As CountOfEntry From attendance Where [Date] Between
DateAdd("m",-3,Date()) And Date()

Just copy this SQL and try it
***************************
Note: It's not good to use reserved words in Access (Like Date) as Fields
names, it requires you to put it in square brackets

 

[Guest]

Dale,

My deepest thanks to you for your assistance on this. It works BEAUTIFULLY.
I will endeavor to name my first born after you...oops, already did.

Thanks so very much.

Richard

Doom,

Something like the following should work. Note that I actually subtracted
90 days from the the date field in your table, so this assumes that you want
90 continuous days. If you want 90 weekdays this becomes a little more
difficult.

Select T1.DateField, AVG(T2.SomeOtherField) as 90DayMovingAverage
FROM yourTable T1, yourTable T2
WHERE T2.DateField BETWEEN DateAdd("d", -90, T1.DateField) AND T1.DateField
GROUP BY T1.DateField

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

That works beautifly - almost. It will give me the 90 day average for today.
Very nice, thank you.

Is there a way to generate a 90 day average for each day in the database?

PS - when I started writing this "Frankenstein," it was just designed to
keep attendance, so the field names weren't that important. (Poor form, I
know. But at the time, I had never used Access before [old DBMS programmer
of FoxPro and dBase].) I want to change it, and will take the time to do
that here soon...or write the whole thing in VB.

Thanks again.

Let start with the fact that
DateSerial(Year([Date]),Month([Date])-3,Day([Date]))

Is not a good way of getting the start date

1/1/2007 will return -2 for the month

So, you should have used the DateAdd function to subtruct 3 month's from the
date
DateAdd("m",-3,[Date])

***************************
But for your case, if you want the number of entries in the last three
month's try

Select Count(*) As CountOfEntry From attendance Where [Date] Between
DateAdd("m",-3,Date()) And Date()

Just copy this SQL and try it
***************************
Note: It's not good to use reserved words in Access (Like Date) as Fields
names, it requires you to put it in square brackets

 

[Marshall Barton]

If you want the count of previous 3 months for each record,
you will need to use a subquery.

SELECT A.[Date], A.f1, A.f2, ... ,
(SELECT Count(*)
FROM attendance As X
WHERE X.[Date] Between
DateAdd("m",-3, A.[Date]) And A.[Date]
) As CountOfEntry
FROM attendance As A
--
Marsh
MVP [MS Access]

That works beautifly - almost. It will give me the 90 day average for today.
Very nice, thank you.

Is there a way to generate a 90 day average for each day in the database?

But for your case, if you want the number of entries in the last three
month's try

Select Count(*) As CountOfEntry From attendance Where [Date] Between
DateAdd("m",-3,Date()) And Date()

 

[Dale Fye]

Glad I could help.

Dale

Dale,

My deepest thanks to you for your assistance on this. It works
BEAUTIFULLY.
I will endeavor to name my first born after you...oops, already did.

Thanks so very much.

Richard

Doom,

Something like the following should work. Note that I actually
subtracted
90 days from the the date field in your table, so this assumes that you
want
90 continuous days. If you want 90 weekdays this becomes a little more
difficult.

Select T1.DateField, AVG(T2.SomeOtherField) as 90DayMovingAverage
FROM yourTable T1, yourTable T2
WHERE T2.DateField BETWEEN DateAdd("d", -90, T1.DateField) AND
T1.DateField
GROUP BY T1.DateField

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

That works beautifly - almost. It will give me the 90 day average for
today.
Very nice, thank you.

Is there a way to generate a 90 day average for each day in the
database?

PS - when I started writing this "Frankenstein," it was just designed
to
keep attendance, so the field names weren't that important. (Poor
form, I
know. But at the time, I had never used Access before [old DBMS
programmer
of FoxPro and dBase].) I want to change it, and will take the time to
do
that here soon...or write the whole thing in VB.

Thanks again.

:

Let start with the fact that
DateSerial(Year([Date]),Month([Date])-3,Day([Date]))

Is not a good way of getting the start date

1/1/2007 will return -2 for the month

So, you should have used the DateAdd function to subtruct 3 month's
from the
date
DateAdd("m",-3,[Date])

***************************
But for your case, if you want the number of entries in the last
three
month's try

Select Count(*) As CountOfEntry From attendance Where [Date] Between
DateAdd("m",-3,Date()) And Date()

Just copy this SQL and try it
***************************
Note: It's not good to use reserved words in Access (Like Date) as
Fields
names, it requires you to put it in square brackets