average last 3 months in query

[buzzmcduffie]

I have a query that has, by person, a score for each month. Some people have
scores for Oct, Nov, Dec but some people have only May, June, July. I need
to average what ever the last 3 months of scores for each person no matter
what the last three months are.
fields are:
person; month; score
So, the results need to be: by person their last 3 month scores no matter
what those months are. Then I can average them.

 

[Roger Carlson]

You should be able to do that with a TOP query by person. You don't give
any specifics, so I can't either, but on my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"TopQuery.mdb" which might help. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[buzzmcduffie]

Roger - I've been trying to get it to work with no luck.

My query is called:
qryQA_MonthlyAuditResults

My fields are:
Task
EmployeeID
LoggedMonthYear
AccuracyScore

I need an average of the last three month's records (no matter what the last
3 months were) for each EmployeeId and their Task.

Let me know if I need to be more specific.

Thanks from an inexperienced query writer!

 

[John W. Vinson]

Roger - I've been trying to get it to work with no luck.

My query is called:
qryQA_MonthlyAuditResults

My fields are:
Task
EmployeeID
LoggedMonthYear
AccuracyScore

I need an average of the last three month's records (no matter what the last
3 months were) for each EmployeeId and their Task.

What are the datatypes and contents of these fields, in particular
LoggedMonthYear? A Date/Time field would be ideal; what's in the field now?

 

[buzzmcduffie]

John

Task = text
EmployeeID = Text with employee initials (ABS)
LoggedMonthYear = date/time with content = mm/yyyy (04/2008)
AccuracyScore = Percentage w/ 2 decimals (78.57%)

thanks for any help

 

[John W. Vinson]

John

Task = text
EmployeeID = Text with employee initials (ABS)
LoggedMonthYear = date/time with content = mm/yyyy (04/2008)
AccuracyScore = Percentage w/ 2 decimals (78.57%)

thanks for any help

Create a Query using all four fields.
Change it to a Totals query by clicking the Greek Sigma icon (looks like a
sideways M).
Leave the default Group By on Task and EmployeeID.
Change it to Avg on AccuracyScore.
Change it to Where on LoggedMonthYear.

Put a criterion of

= DateAdd("m", -3, Date())

to get the literal last three months data (today, from 11/7/08 through
12/7/08); or

= DateSerial(Year(Date()), Month(Date()) - 3, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

to get the previous three months' data (9/1/08 through 11/30/08).

 

[buzzmcduffie]

thanks John,
what if there isn't any data for one of last 3 months? I want to then go to
the last 3 months that had data. For instance 1 emp has Nov, Oct, & Sept but
another has just Oct, Sept and Aug. I really want the last 3 months that had
data, not just the last 3 months.

 

[John W. Vinson]

thanks John,
what if there isn't any data for one of last 3 months? I want to then go to
the last 3 months that had data. For instance 1 emp has Nov, Oct, & Sept but
another has just Oct, Sept and Aug. I really want the last 3 months that had
data, not just the last 3 months.

Sorry... that wasn't how I read the question!

What if there is data in May 2003, August 2005 and January 2008? Do you want
those three months?

Or do you want the most recent set of three contiguous months?

 

[buzzmcduffie]

thanks John

I would like the last 3 months that had data..

Emp 1 had data in Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had data in Jan Feb Apr May - I want to see Feb, Apr, May
Everyone is going to be alittle different.

Sorry I wasn't clear..probably still not.

 

[John W. Vinson]

thanks John

I would like the last 3 months that had data..

Emp 1 had data in Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had data in Jan Feb Apr May - I want to see Feb, Apr, May
Everyone is going to be alittle different.

Sorry I wasn't clear..probably still not.

That's going to be pretty complicated. You'll need to create a subsidiary
query with one record per month, per employee; use a calculated field

FirstOfMonth: DateSerial(Year([datefield]), Month([datefield]), 1)

Create a totals query grouping by the employeeID and this field to get one
record per month. Include any totals for the month that you want to include in
the report.

Then, use this query in a Subquery using the TOP VALUES property of the
subquery.

I'll try to put some SQL together for you tonight or tomorrow, I don't have
the energy right now!

 

[buzzmcduffie]

OK John
I've gotten this far:

SELECT qryQA_RollingMonths1.Task, qryQA_RollingMonths1.EmployeeID,
qryQA_RollingMonths1.LoggedMonthYear, qryQA_RollingMonths1.AccuracyScore,
DateSerial(Year([LoggedMonthYear]),Month([LoggedMonthYear]),1) AS FirstOfMonth
FROM qryQA_RollingMonths1;

now what?
thanks!

thanks John

I would like the last 3 months that had data..

Emp 1 had data in Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had data in Jan Feb Apr May - I want to see Feb, Apr, May
Everyone is going to be alittle different.

Sorry I wasn't clear..probably still not.

That's going to be pretty complicated. You'll need to create a subsidiary
query with one record per month, per employee; use a calculated field

FirstOfMonth: DateSerial(Year([datefield]), Month([datefield]), 1)

Create a totals query grouping by the employeeID and this field to get one
record per month. Include any totals for the month that you want to include in
the report.

Then, use this query in a Subquery using the TOP VALUES property of the
subquery.

I'll try to put some SQL together for you tonight or tomorrow, I don't have
the energy right now!

 

[buzzmcduffie]

I still need help with the subquery if anyone is up to the challenge of
teaching a novice to get a rolling average.

thanks!

OK John
I've gotten this far:

SELECT qryQA_RollingMonths1.Task, qryQA_RollingMonths1.EmployeeID,
qryQA_RollingMonths1.LoggedMonthYear, qryQA_RollingMonths1.AccuracyScore,
DateSerial(Year([LoggedMonthYear]),Month([LoggedMonthYear]),1) AS FirstOfMonth
FROM qryQA_RollingMonths1;

now what?
thanks!

thanks John

I would like the last 3 months that had data..

Emp 1 had data in Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had data in Jan Feb Apr May - I want to see Feb, Apr, May
Everyone is going to be alittle different.

Sorry I wasn't clear..probably still not.

That's going to be pretty complicated. You'll need to create a subsidiary
query with one record per month, per employee; use a calculated field

FirstOfMonth: DateSerial(Year([datefield]), Month([datefield]), 1)

Create a totals query grouping by the employeeID and this field to get one
record per month. Include any totals for the month that you want to include in
the report.

Then, use this query in a Subquery using the TOP VALUES property of the
subquery.

I'll try to put some SQL together for you tonight or tomorrow, I don't have
the energy right now!