rolling 3 months

[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!

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!

.

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.


John W. Vinson [MVP]

 

[Dale Fye]

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.

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

thanks!

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!

.

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.


John W. Vinson [MVP]

 

[buzzmcduffie]

employeeID - text
LoggedMonthYear - date
AccuracyScore - percentage

One record per employee per month. Not all employees have a record for
every month. I need the average per employee for the last 3 months of DATA
not actual months.

Emp 1 had a score for Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had a score for Jan Feb (does not have a score for March) Apr May - I
want to see Feb, Apr, May

I want Employee 1 average score for the last 3 months of data (MAR, APR, MAY)
I want Employee 2's average score for the last 3 months of data. (FEB, APR,
MAY)

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?
Or do I give each record a number - month 1 of data, month 2 of data etc.

thanks for whatever you can do..I'm stuck

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.

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

thanks!

I would like the last 3 months that had data for each employee..

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.

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!

.

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.


John W. Vinson [MVP]

 

[Dale Fye]

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?

Do you mean that you have records for the EmployeeID, where
[LoggedMonthYear] is NULL? Or do you mean that you have values in that field
like 1/10/08, 2/15/08, 4/3/08, 5/23/08?

If my tests are correct, the following should work.

SELECT yourTableName.EmployeeID,
Avg(yourTableName.[AccuracyScore]) AS RollingAvg,
Count(yourTableName.[AccuracyScore]) AS RecCount
FROM yourTableName
INNER JOIN (SELECT T.EmployeeID, T.LoggedMonthYear,
DCount("EmployeeID","yourTableName","EmployeeID = " & [T].[EmployeeID] & "
AND [LoggedMonthYear] >= #" & [T].[LoggedMonthYear] & "#") AS Rank
FROM yourTableName AS T
WHERE (((DCount("EmployeeID","yourTableName","EmployeeID = " &
[T].[EmployeeID] & " AND [LoggedMonthYear] >= #" & [T].[LoggedMonthYear] &
"#"))<4))
ORDER BY T.LoggedMonthYear DESC) AS Last3 ON (yourTableName.LoggedMonthYear
= Last3.LoggedMonthYear) AND (yourTableName.EmployeeID = Last3.EmployeeID)
GROUP BY yourTableName.EmployeeID;

This uses the DCOUNT function to identify a ranking (based on the # of
records with a LoggedMonthYear value >= the current record) for each entry
for each employee. This gives you the ability to filter on Rank < 4. It
then joins the table to this subquery, and computes the average accuracy and
provides you with a count of the number of records that were included in the
average (for those that have less then 3 entries).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

employeeID - text
LoggedMonthYear - date
AccuracyScore - percentage

One record per employee per month. Not all employees have a record for
every month. I need the average per employee for the last 3 months of DATA
not actual months.

Emp 1 had a score for Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had a score for Jan Feb (does not have a score for March) Apr May - I
want to see Feb, Apr, May

I want Employee 1 average score for the last 3 months of data (MAR, APR, MAY)
I want Employee 2's average score for the last 3 months of data. (FEB, APR,
MAY)

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?
Or do I give each record a number - month 1 of data, month 2 of data etc.

thanks for whatever you can do..I'm stuck

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.

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

thanks!

:
I would like the last 3 months that had data for each employee..

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.


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!

.

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.


John W. Vinson [MVP]

 

[John Spencer]

Assuming you have zero to one record per employee per month. You could try
using a coorelated subquery to identify the last three months per employee.

SELECT YourTable.EmployeeID
, Count(LoggedMonthYear) as NumberOfMonths
, Min(LoggedMonthYear) as PeriodBegin
, Max(LoggedMonthYear) as PeriodEnd
, Avg(AccuracyScore) as AvgScore
FROM YourTable
WHERE LoggedMonthYear in
(SELECT TOP 3 LoggedMonthYear
FROM YourTable as Temp
WHERE Temp.EmployeeID = YourTable.EmployeeID
ORDER BY LoggedMonthYear DESC)
GROUP BY YourTable.EmployeeID

I added a few more items in to give you more information.
Count(LoggedMonthYear) will tell you if 1, 2, or 3 months were used in
calculating the average.

Min and Max will give you the first month and last month in the period.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

employeeID - text
LoggedMonthYear - date
AccuracyScore - percentage

One record per employee per month. Not all employees have a record for
every month. I need the average per employee for the last 3 months of DATA
not actual months.

Emp 1 had a score for Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had a score for Jan Feb (does not have a score for March) Apr May - I
want to see Feb, Apr, May

I want Employee 1 average score for the last 3 months of data (MAR, APR, MAY)
I want Employee 2's average score for the last 3 months of data. (FEB, APR,
MAY)

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?
Or do I give each record a number - month 1 of data, month 2 of data etc.

thanks for whatever you can do..I'm stuck

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.

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

thanks!

:
I would like the last 3 months that had data for each employee..

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.
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!

.
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.


John W. Vinson [MVP]

 

[Dale Fye]

Very nice, John. Much cleaner and easier to read than my idea.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Assuming you have zero to one record per employee per month. You could try
using a coorelated subquery to identify the last three months per employee.

SELECT YourTable.EmployeeID
, Count(LoggedMonthYear) as NumberOfMonths
, Min(LoggedMonthYear) as PeriodBegin
, Max(LoggedMonthYear) as PeriodEnd
, Avg(AccuracyScore) as AvgScore
FROM YourTable
WHERE LoggedMonthYear in
(SELECT TOP 3 LoggedMonthYear
FROM YourTable as Temp
WHERE Temp.EmployeeID = YourTable.EmployeeID
ORDER BY LoggedMonthYear DESC)
GROUP BY YourTable.EmployeeID

I added a few more items in to give you more information.
Count(LoggedMonthYear) will tell you if 1, 2, or 3 months were used in
calculating the average.

Min and Max will give you the first month and last month in the period.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

employeeID - text
LoggedMonthYear - date
AccuracyScore - percentage

One record per employee per month. Not all employees have a record for
every month. I need the average per employee for the last 3 months of DATA
not actual months.

Emp 1 had a score for Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had a score for Jan Feb (does not have a score for March) Apr May - I
want to see Feb, Apr, May

I want Employee 1 average score for the last 3 months of data (MAR, APR, MAY)
I want Employee 2's average score for the last 3 months of data. (FEB, APR,
MAY)

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?
Or do I give each record a number - month 1 of data, month 2 of data etc.

thanks for whatever you can do..I'm stuck

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.



:

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

thanks!

:
I would like the last 3 months that had data for each employee..

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.
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!

.
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.


John W. Vinson [MVP]

 

[buzzmcduffie]

let me give it a try - you guys are my hero!!!!

Assuming you have zero to one record per employee per month. You could try
using a coorelated subquery to identify the last three months per employee.

SELECT YourTable.EmployeeID
, Count(LoggedMonthYear) as NumberOfMonths
, Min(LoggedMonthYear) as PeriodBegin
, Max(LoggedMonthYear) as PeriodEnd
, Avg(AccuracyScore) as AvgScore
FROM YourTable
WHERE LoggedMonthYear in
(SELECT TOP 3 LoggedMonthYear
FROM YourTable as Temp
WHERE Temp.EmployeeID = YourTable.EmployeeID
ORDER BY LoggedMonthYear DESC)
GROUP BY YourTable.EmployeeID

I added a few more items in to give you more information.
Count(LoggedMonthYear) will tell you if 1, 2, or 3 months were used in
calculating the average.

Min and Max will give you the first month and last month in the period.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

employeeID - text
LoggedMonthYear - date
AccuracyScore - percentage

One record per employee per month. Not all employees have a record for
every month. I need the average per employee for the last 3 months of DATA
not actual months.

Emp 1 had a score for Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had a score for Jan Feb (does not have a score for March) Apr May - I
want to see Feb, Apr, May

I want Employee 1 average score for the last 3 months of data (MAR, APR, MAY)
I want Employee 2's average score for the last 3 months of data. (FEB, APR,
MAY)

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?
Or do I give each record a number - month 1 of data, month 2 of data etc.

thanks for whatever you can do..I'm stuck

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.



:

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

thanks!

:
I would like the last 3 months that had data for each employee..

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.
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!

.
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.


John W. Vinson [MVP]

 

[John W. Vinson]

Assuming you have zero to one record per employee per month. You could try
using a coorelated subquery to identify the last three months per employee.

SELECT YourTable.EmployeeID
, Count(LoggedMonthYear) as NumberOfMonths
, Min(LoggedMonthYear) as PeriodBegin
, Max(LoggedMonthYear) as PeriodEnd
, Avg(AccuracyScore) as AvgScore
FROM YourTable
WHERE LoggedMonthYear in
(SELECT TOP 3 LoggedMonthYear
FROM YourTable as Temp
WHERE Temp.EmployeeID = YourTable.EmployeeID
ORDER BY LoggedMonthYear DESC)
GROUP BY YourTable.EmployeeID

Very elegant, John - thanks. I was trying to get up my courage to try a
top-values correlated subquery...

 

[buzzmcduffie]

ok this is my SQL but it's not giving me the correct answers that I have in
Excel that is proofed -
This table has info going back to 2007. maybe it's not pulling the last 3
months for each emp.
Can you check my SQL

SELECT tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID,
Count(tblQA_RollingMonths.LoggedMonthYear) AS NumberOfMonths,
Min(tblQA_RollingMonths.LoggedMonthYear) AS PeriodBegin,
Max(tblQA_RollingMonths.LoggedMonthYear) AS PeriodEnd,
Avg(tblQA_RollingMonths.AccuracyScore) AS AvgScore
FROM tblQA_RollingMonths
WHERE (((tblQA_RollingMonths.LoggedMonthYear) In (SELECT TOP 3
tblQA_RollingMonths.LoggedMonthYear
FROM tblQA_RollingMonths as Temp
WHERE Temp.EmployeeID = tblQA_RollingMonths.EmployeeID
ORDER BY tblQA_RollingMonths.LoggedMonthYear DESC)))
GROUP BY tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID;

let me give it a try - you guys are my hero!!!!

Assuming you have zero to one record per employee per month. You could try
using a coorelated subquery to identify the last three months per employee.

SELECT YourTable.EmployeeID
, Count(LoggedMonthYear) as NumberOfMonths
, Min(LoggedMonthYear) as PeriodBegin
, Max(LoggedMonthYear) as PeriodEnd
, Avg(AccuracyScore) as AvgScore
FROM YourTable
WHERE LoggedMonthYear in
(SELECT TOP 3 LoggedMonthYear
FROM YourTable as Temp
WHERE Temp.EmployeeID = YourTable.EmployeeID
ORDER BY LoggedMonthYear DESC)
GROUP BY YourTable.EmployeeID

I added a few more items in to give you more information.
Count(LoggedMonthYear) will tell you if 1, 2, or 3 months were used in
calculating the average.

Min and Max will give you the first month and last month in the period.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

employeeID - text
LoggedMonthYear - date
AccuracyScore - percentage

One record per employee per month. Not all employees have a record for
every month. I need the average per employee for the last 3 months of DATA
not actual months.

Emp 1 had a score for Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had a score for Jan Feb (does not have a score for March) Apr May - I
want to see Feb, Apr, May

I want Employee 1 average score for the last 3 months of data (MAR, APR, MAY)
I want Employee 2's average score for the last 3 months of data. (FEB, APR,
MAY)

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?
Or do I give each record a number - month 1 of data, month 2 of data etc.

thanks for whatever you can do..I'm stuck


:

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.



:

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

thanks!

:
I would like the last 3 months that had data for each employee..

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.
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!

.
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.


John W. Vinson [MVP]

 

[buzzmcduffie]

I just figured out it's averaging all months for the employee, not just the
last 3 months that are on the table for that employee.
To answer a question from above - not all employees have a record for all
the months.

 

[John Spencer]

First step is to see if you are returning the correct records before doing the
aggregation:

SELECT tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID,
tblQA_RollingMonths.LoggedMonthYear
FROM tblQA_RollingMonths
WHERE (((tblQA_RollingMonths.LoggedMonthYear) In (SELECT TOP 3
tblQA_RollingMonths.LoggedMonthYear
FROM tblQA_RollingMonths as Temp
WHERE Temp.EmployeeID = tblQA_RollingMonths.EmployeeID
ORDER BY tblQA_RollingMonths.LoggedMonthYear DESC)))
Order by Task, EmployeeID, LoggedMonthYear

Is that returning 3 records for each employee?

If you get 3 records but not the right three records, I would be suspicious of
the field LoggedMonthYear. Is that a date field showing a month, a day, and a
year? Or did you convert it to a string by applying a format to a date field?

Where did TASK come from? Are you trying to get the average by employee by
task? If so, the query needs further modification to take that into account.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

ok this is my SQL but it's not giving me the correct answers that I have in
Excel that is proofed -
This table has info going back to 2007. maybe it's not pulling the last 3
months for each emp.
Can you check my SQL

SELECT tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID,
Count(tblQA_RollingMonths.LoggedMonthYear) AS NumberOfMonths,
Min(tblQA_RollingMonths.LoggedMonthYear) AS PeriodBegin,
Max(tblQA_RollingMonths.LoggedMonthYear) AS PeriodEnd,
Avg(tblQA_RollingMonths.AccuracyScore) AS AvgScore
FROM tblQA_RollingMonths
WHERE (((tblQA_RollingMonths.LoggedMonthYear) In (SELECT TOP 3
tblQA_RollingMonths.LoggedMonthYear
FROM tblQA_RollingMonths as Temp
WHERE Temp.EmployeeID = tblQA_RollingMonths.EmployeeID
ORDER BY tblQA_RollingMonths.LoggedMonthYear DESC)))
GROUP BY tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID;

let me give it a try - you guys are my hero!!!!

Assuming you have zero to one record per employee per month. You could try
using a coorelated subquery to identify the last three months per employee.

SELECT YourTable.EmployeeID
, Count(LoggedMonthYear) as NumberOfMonths
, Min(LoggedMonthYear) as PeriodBegin
, Max(LoggedMonthYear) as PeriodEnd
, Avg(AccuracyScore) as AvgScore
FROM YourTable
WHERE LoggedMonthYear in
(SELECT TOP 3 LoggedMonthYear
FROM YourTable as Temp
WHERE Temp.EmployeeID = YourTable.EmployeeID
ORDER BY LoggedMonthYear DESC)
GROUP BY YourTable.EmployeeID

I added a few more items in to give you more information.
Count(LoggedMonthYear) will tell you if 1, 2, or 3 months were used in
calculating the average.

Min and Max will give you the first month and last month in the period.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

buzzmcduffie wrote:
employeeID - text
LoggedMonthYear - date
AccuracyScore - percentage

One record per employee per month. Not all employees have a record for
every month. I need the average per employee for the last 3 months of DATA
not actual months.

Emp 1 had a score for Jan Feb Mar Apr May = I want to see Mar, Apr, May
Emp 2 had a score for Jan Feb (does not have a score for March) Apr May - I
want to see Feb, Apr, May

I want Employee 1 average score for the last 3 months of data (MAR, APR, MAY)
I want Employee 2's average score for the last 3 months of data. (FEB, APR,
MAY)

Is it something to do with if last month is null, go to the previous month,
if that month is null, go to the previous month?
Or do I give each record a number - month 1 of data, month 2 of data etc.

thanks for whatever you can do..I'm stuck


:

I'd be willing to give it a shot, but need a lot more information than what
you have provided here. What is your table structure? Is there one, or more
than one record per month for each employee? Can you provide some sample
data, and what you want the query output to look like for that sample data?

--
Dale

email address is invalid
Please reply to newsgroup only.



:

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

thanks!

:
I would like the last 3 months that had data for each employee..

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.
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!

.
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.


John W. Vinson [MVP]

 

[Dale Fye]

I'm with John (Spencer). You've thrown the Task into the mix that was not
there before.

I think you can fix it by replacing the subquery with:

(SELECT TOP 3 LoggedMonthYear
FROM YourTable as Temp
WHERE Temp.EmployeeID = YourTable.EmployeeID
AND Temp.TaskID = yourTable.TaskID
ORDER BY LoggedMonthYear DESC)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[buzzmcduffie]

It is still giving me all the months avg - not the last 3 months of records
Here's my sql

SELECT tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID,
Count(tblQA_RollingMonths.LoggedMonthYear) AS NumberOfMonths,
Min(tblQA_RollingMonths.LoggedMonthYear) AS PeriodBegin,
Max(tblQA_RollingMonths.LoggedMonthYear) AS PeriodEnd,
Avg(tblQA_RollingMonths.AccuracyScore) AS AvgScore
FROM tblQA_RollingMonths
WHERE (((tblQA_RollingMonths.LoggedMonthYear) In (SELECT TOP 3
tblQA_RollingMonths.LoggedMonthYear
FROM tblQA_RollingMonths as Temp
WHERE Temp.EmployeeID = tblQA_RollingMonths.EmployeeID and Temp.Task =
tblQA_RollingMonths.Task
ORDER BY tblQA_RollingMonths.LoggedMonthYear DESC)))
GROUP BY tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID;

Here's a sample of my table

Task EmployeeID LoggedMonthYear AccuracyScore
AH Payor ABS 05/2008 88.00%
AH Payor ABS 06/2008 94.00%
AH Payor ABS 07/2008 80.00%
AH Payor ABS 08/2008 87.14%
AH Payor ABS 09/2008 78.57%
AH Payor ABS 10/2008 95.00%
AH Payor ABS 11/2008 85.71%
AH Payor ALS 04/2008 100.00%
AH Payor ALS 05/2008 88.00%
AH Payor ALS 06/2008 100.00%
AH Payor ALS 07/2008 86.00%
AH Payor ALS 08/2008 87.14%
AH Payor ALS 10/2008 100.00%
AH Payor ALS 11/2008 95.71%

for ABS - I want an average of 9/08 - 11/08
for ALS - I want an average of 8/08, 10/08 and 11/08

 

[Dale Fye]

Well, you indicated that your LoggedMonthYear field was a date field, not a
string. Do you have data for other years in this table as well, or is it all
2008?

Will each employee only be working on one task during the year, or will they
show up on multiple tasks? If they can have multiple tasks, will you have a
separate score for each employee, for each task, and potentially different
LoggedMonthYear values?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[buzzmcduffie]

Dale,
It's really a query not a table. The query gathers every record during the
month and creates a weighted score. Therefore the field is mm/yyyy. i have
years in 2007 through present and it will continue to grow.
The employees will have 1 task per month but yes, they could have a record
doing one thing one month and another thing another month. The employees
that do multiple tasks over a period of time will have an average for each
task. Does this help?

 

[Dale Fye]

So, the values you are showing in your example are already weighted averages,
and then you want the average of the most recent 3 weighted averages?

Yes, and it explains why the queries are not returning the values you
expect, since the TOP 3, sorted descending will return "12/2008", "12/2007",
"11/2008" if there are records for those months.

If neither of the Johns chime in before I get home, I'll take a look at it
tonight.
--

Dale

email address is invalid
Please reply to newsgroup only.

 

[buzzmcduffie]

Thanks Dale! Yes I know it's an average of an average but we are just
looking at it for trending. If I just look at the year 2008 then how will I
get Jan, Dec, Nov data? I can't tell you guys how much I appreciate this
hellp!

 

[Dale Fye]

OK,

Try changing the order by clause in the sub query to:

ORDER BY cdate([loggedmonthyear]) DESC

I played around with a couple of things, and never thought that this would
really work, but the cdate( ) function appears to interpret "08/2008" as 1
Aug, 2008. Who knew?

Dale

 

[buzzmcduffie]

Dale,
I know you are really getting frustrated with me but this help is invaluable
to me!

I must have put the cdate statement in the wrong place because it's still
giving me an average of all dates - not just the last 3 months of data..

SELECT tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID,
Count(tblQA_RollingMonths.LoggedMonthYear) AS NumberOfMonths,
Min(tblQA_RollingMonths.LoggedMonthYear) AS PeriodBegin,
Max(tblQA_RollingMonths.LoggedMonthYear) AS PeriodEnd,
Avg(tblQA_RollingMonths.AccuracyScore) AS AvgScore
FROM tblQA_RollingMonths
WHERE ((([tblQA_RollingMonths].[LoggedMonthYear]) In (SELECT TOP 3
tblQA_RollingMonths.LoggedMonthYear
FROM tblQA_RollingMonths as Temp
WHERE Temp.EmployeeID = tblQA_RollingMonths.EmployeeID and Temp.Task =
tblQA_RollingMonths.Task
ORDER BY cdate(tblQA_RollingMonths.LoggedMonthYear) DESC)))
GROUP BY tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID;

can we spit it up so we first get the last 3 months of data for each and
then query to get the average so I can see what months are produced for each
employee?

Thanks!

OK,

Try changing the order by clause in the sub query to:

ORDER BY cdate([loggedmonthyear]) DESC

I played around with a couple of things, and never thought that this would
really work, but the cdate( ) function appears to interpret "08/2008" as 1
Aug, 2008. Who knew?

Dale

Thanks Dale! Yes I know it's an average of an average but we are just
looking at it for trending. If I just look at the year 2008 then how will
I
get Jan, Dec, Nov data? I can't tell you guys how much I appreciate this
hellp!

 

[John Spencer]

Try the following. Note the changes in the subquery where I specified TEMP as
the table name. You had

SELECT tblQA_RollingMonths.Task
, tblQA_RollingMonths.EmployeeID
, Count(tblQA_RollingMonths.LoggedMonthYear) AS CountMonths
, Min(tblQA_RollingMonths.LoggedMonthYear) AS PeriodBegin
, Max(tblQA_RollingMonths.LoggedMonthYear) AS PeriodEnd
, Avg(tblQA_RollingMonths.AccuracyScore) AS AvgScore
FROM tblQA_RollingMonths
WHERE tblQA_RollingMonths.LoggedMonthYear In
(SELECT TOP 3 TEMP.LoggedMonthYear
FROM tblQA_RollingMonths as Temp
WHERE TEMP.EmployeeID = tblQA_RollingMonths.EmployeeID
and TEMP.Task = tblQA_RollingMonths.Task
ORDER BY cdate(TEMP.LoggedMonthYear) DESC)
GROUP BY tblQA_RollingMonths.Task
, tblQA_RollingMonths.EmployeeID;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County