rolling 3 months query

[buzzmcduffie]

I have a query that produces results of the total number of errors each
employee has made in 5 categories for each month. I need a rolling 3 month
total for each employee for each category in a crosstab query

i.e.
Rolling Nov total for each category for each emp, rolling Dec total for each
category for each emp, rolling Jan total for each category....etc...(I used
this example to explain that it will go past a calendar year...our year goes
from Nov - Oct)

row = emp
columns = each rolling 3 mm/yyyy for Nov through Oct
value = # of errors for each category

My fields in the query are:
[Emp]
[Month]
[Year]
[#Category_A_Errors]
[#Category_B_Errors]
[#Category_C_Errors]
[#Category_D_Errors]
[#Category_E_Errors]

I'm a newbie and have tried to understand the posts regarding this but I'm
still confused.

Any help would be great!

 

[KARL DEWEY]

You need to be using a DateTime field instead of separate month and year
fields.
Then you could use the DateAdd function for the rolling 5 months.

For last month and 4 more back your criteria would be ---
Between DateAdd("m", -5, Date()-Day(Date())+1) And Date()-Day(Date())

Day(Date()) returns the day of the month - 28 for today.
Date()-Day(Date()) returns the last day of last month.
Date()-Day(Date())+1 adds one day to have first day of this month.
DateAdd("m", -5, Date()-Day(Date())+1) returns the first day of 5 months ago.

 

[Jerry Whittle]

Assuming that the fields in the query are also in the table, your table is
not designed properly. This will cause you problems.

First of all you should not have separate Year and Month fields. Rather you
should have one date/time field. Access has plenty of good tools to work with
dates. In fact the subject of this discussion, "rolling 3 months query" would
be much easier to do if you had just one date/time field.

Next you have data in your field names. In other words you are creating
columns across instead of data down. Answer this: after you have all your
queries, forms, and reports built, someone decides to add a
[[#Category_F_Errors] field? Messes everything up, doesn't it!

The table should look something like below. Make the error date the first
day of the month for consistancy sake. It will make the crosstab look better:

Emp ErrorDate ErrorType NumberErrors
123 1 Nov 07 A 5
123 1 Nov 07 B 3
456 1 Nov 07 A 2
456 1 Oct 07 A 1
789 1 Sep 07 C 5

The the query to extract the last three month's data would look something
like:

SELECT Buzzmcduffie.Emp,
Buzzmcduffie.ErrorDate,
Buzzmcduffie.ErrorType,
Buzzmcduffie.NumberErrors
FROM Buzzmcduffie
WHERE Buzzmcduffie.ErrorDate>DateAdd("m",-3,Date());

Save the query to a name something like qryBuzzmcduffie. Next make a
crosstab query based on qryBuzzmcduffie. The SQL would look something like:

TRANSFORM Sum(qryBuzzmcduffie.NumberErrors) AS SumOfNumberErrors
SELECT qryBuzzmcduffie.Emp, qryBuzzmcduffie.ErrorType,
Sum(qryBuzzmcduffie.NumberErrors) AS [Total Of NumberErrors]
FROM qryBuzzmcduffie
GROUP BY qryBuzzmcduffie.Emp, qryBuzzmcduffie.ErrorType
PIVOT [ErrorDate];

And the results:

Emp ErrorType TotalErrors 9/1/2007 10/1/2007 11/1/2007
123 A 5 5
123 B 3 3
456 A 3 1 2
789 C 5 5

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that produces results of the total number of errors each
employee has made in 5 categories for each month. I need a rolling 3 month
total for each employee for each category in a crosstab query

i.e.
Rolling Nov total for each category for each emp, rolling Dec total for each
category for each emp, rolling Jan total for each category....etc...(I used
this example to explain that it will go past a calendar year...our year goes
from Nov - Oct)

row = emp
columns = each rolling 3 mm/yyyy for Nov through Oct
value = # of errors for each category

My fields in the query are:
[Emp]
[Month]
[Year]
[#Category_A_Errors]
[#Category_B_Errors]
[#Category_C_Errors]
[#Category_D_Errors]
[#Category_E_Errors]

I'm a newbie and have tried to understand the posts regarding this but I'm
still confused.

Any help would be great!

 

[buzzmcduffie]

I'm sorry I was so confusing.

This query I described is after many other queries. My original tables are
below:

tblAuditData
[Key] = autonumber
[ExamID] = text
[LoggedDate] = date
[NoError] = yes/no

tblErrorData
[key] = text
[errorcode] = text

tblErrors
[errorcode] = text
[errorcategory] = text
[errordescription]= text

I have a query that counts the total number of audits performed for each
examiner. I have a query that translates the errors and their categories.
And here is where I think I have gone astray:
I then have 5 queries that count how many errors each examiner has for each
category. I then take those queries and join them with the total number of
audits query and the result is a query I described below.

Please help. I've never worked in access before and I'm trying to automate
an Excel spreadsheet that is a nightmare to report off of.

I'm going to go back and try the suggestions below and see if it works.
thanks!

Assuming that the fields in the query are also in the table, your table is
not designed properly. This will cause you problems.

First of all you should not have separate Year and Month fields. Rather you
should have one date/time field. Access has plenty of good tools to work with
dates. In fact the subject of this discussion, "rolling 3 months query" would
be much easier to do if you had just one date/time field.

Next you have data in your field names. In other words you are creating
columns across instead of data down. Answer this: after you have all your
queries, forms, and reports built, someone decides to add a
[[#Category_F_Errors] field? Messes everything up, doesn't it!

The table should look something like below. Make the error date the first
day of the month for consistancy sake. It will make the crosstab look better:

Emp ErrorDate ErrorType NumberErrors
123 1 Nov 07 A 5
123 1 Nov 07 B 3
456 1 Nov 07 A 2
456 1 Oct 07 A 1
789 1 Sep 07 C 5

The the query to extract the last three month's data would look something
like:

SELECT Buzzmcduffie.Emp,
Buzzmcduffie.ErrorDate,
Buzzmcduffie.ErrorType,
Buzzmcduffie.NumberErrors
FROM Buzzmcduffie
WHERE Buzzmcduffie.ErrorDate>DateAdd("m",-3,Date());

Save the query to a name something like qryBuzzmcduffie. Next make a
crosstab query based on qryBuzzmcduffie. The SQL would look something like:

TRANSFORM Sum(qryBuzzmcduffie.NumberErrors) AS SumOfNumberErrors
SELECT qryBuzzmcduffie.Emp, qryBuzzmcduffie.ErrorType,
Sum(qryBuzzmcduffie.NumberErrors) AS [Total Of NumberErrors]
FROM qryBuzzmcduffie
GROUP BY qryBuzzmcduffie.Emp, qryBuzzmcduffie.ErrorType
PIVOT [ErrorDate];

And the results:

Emp ErrorType TotalErrors 9/1/2007 10/1/2007 11/1/2007
123 A 5 5
123 B 3 3
456 A 3 1 2
789 C 5 5

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that produces results of the total number of errors each
employee has made in 5 categories for each month. I need a rolling 3 month
total for each employee for each category in a crosstab query

i.e.
Rolling Nov total for each category for each emp, rolling Dec total for each
category for each emp, rolling Jan total for each category....etc...(I used
this example to explain that it will go past a calendar year...our year goes
from Nov - Oct)

row = emp
columns = each rolling 3 mm/yyyy for Nov through Oct
value = # of errors for each category

My fields in the query are:
[Emp]
[Month]
[Year]
[#Category_A_Errors]
[#Category_B_Errors]
[#Category_C_Errors]
[#Category_D_Errors]
[#Category_E_Errors]

I'm a newbie and have tried to understand the posts regarding this but I'm
still confused.

Any help would be great!