rolling 3 months

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

 

[lespaul]

what is the data/format of the information in the table that you are running the query against?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

Did you look at this faq703-5466? Do you realize your table structure isn't normalized?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[SkipVought]

oops! What happens when a [red]Category_F[/red] is discovered???

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AlexCuse]

Guess you'd have to tell the boss you're F'd

[small]----signature below----[/small]
You can't fit a square data in a round table

 

[buzzmcduffie]

This query originally comes from:

tblAuditData
[key]= autonumber
[examid] = text
[loggedate] = date
[NoError] = yes/no

tblErrorData
[key]= number
[errorcode] = text

tblError
[errorcode] = text
[errorcategory] = text
[errordescription] = text

The first query gives me the total number of audits performed for each examiner.

Another query translates the [errorcode] and gives me the [errorcategory] for each error.

Now this is where I have probably screwed up:
I count how many errors from each category the Examiner has by doing 5 queries each counting a different category of error.

The last query gives me results in a count by Examiner by Year and Month the total number Audits performed and the total number of errors for each category.

SELECT qryNumberOfQAAudits.ExamID, qryNumberOfQAAudits.LoggedYear, qryNumberOfQAAudits.LoggedMonth, qryNumberOfQAAudits.NumAudits, qryQACSECountByExam.CSExtErrors, qryQACSICountByExam.CSIntErrors, qryQADataCountByExam.DataErrors, qryQAPaymtCountByExam.PaymtErrors, qryQARiskCountByExam.RiskErrors
FROM ((((qryNumberOfQAAudits LEFT JOIN qryQAPaymtCountByExam ON (qryNumberOfQAAudits.ExamID = qryQAPaymtCountByExam.ExamID) AND (qryNumberOfQAAudits.LoggedMonth = qryQAPaymtCountByExam.LoggedMonth) AND (qryNumberOfQAAudits.LoggedYear = qryQAPaymtCountByExam.LoggedYear)) LEFT JOIN qryQACSICountByExam ON (qryNumberOfQAAudits.ExamID = qryQACSICountByExam.ExamID) AND (qryNumberOfQAAudits.LoggedMonth = qryQACSICountByExam.LoggedMonth) AND (qryNumberOfQAAudits.LoggedYear = qryQACSICountByExam.LoggedYear)) LEFT JOIN qryQACSECountByExam ON (qryNumberOfQAAudits.ExamID = qryQACSECountByExam.ExamID) AND (qryNumberOfQAAudits.LoggedMonth = qryQACSECountByExam.LoggedMonth) AND (qryNumberOfQAAudits.LoggedYear = qryQACSECountByExam.LoggedYear)) LEFT JOIN qryQARiskCountByExam ON (qryNumberOfQAAudits.ExamID = qryQARiskCountByExam.ExamID) AND (qryNumberOfQAAudits.LoggedMonth = qryQARiskCountByExam.LoggedMonth) AND (qryNumberOfQAAudits.LoggedYear = qryQARiskCountByExam.LoggedYear)) LEFT JOIN qryQADataCountByExam ON (qryNumberOfQAAudits.ExamID = qryQADataCountByExam.ExamID) AND (qryNumberOfQAAudits.LoggedMonth = qryQADataCountByExam.LoggedMonth) AND (qryNumberOfQAAudits.LoggedYear = qryQADataCountByExam.LoggedYear)
ORDER BY qryNumberOfQAAudits.Unit, qryNumberOfQAAudits.ExamID, qryNumberOfQAAudits.LoggedYear, qryNumberOfQAAudits.LoggedMonth;

I'm then trying to take those results and doing a rolling 3 months total for the number of audits and the number of errors for each category.

Please help - I've never designed in Access and I'm trying to take an Excel spreadsheet and automate the process for the auditors.

any help is appreciated.

 

[dhookom]

I think you could probably use a crosstab query rather than the multiple queries with LEFT JOINs.

You haven't really described how you expect the results to display.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[lespaul]

Another query translates the [errorcode] and gives me the [errorcategory] for each error.

you shouldn't have to do this in a different query. For example, two tables Customer and Order
[tt]
Customer
ID CompanyName Address PhoneNumber
1 Joe Blow's Shop 123 Main St 5551212

Order
ID CustomerID OrderDate ShipDate
1 1 1/1/2007 1/10/2007[/tt]

If I want to list the order number and the customer name I would use a JOIN:

SELECT Order.ID, Order.CustomerID, Customer.CompanyName
FROM ORDER
INNER JOIN CUSTOMER ON ORDER.CUSTOMERID = CUSTOMER.ID

The results of this query will be:
[tt]
ID CustomerID CompanyName
1 1 Joe Blow's Shop[/tt]

I count how many errors from each category the Examiner has by doing 5 queries each counting a different category of error.

Instead of doing that you can use the GROUP BY clause to perform aggregate functions on a table.

For instance, let's add to the table ORDER above a field called Salesman and another called TotalSale.
[tt]
Order
ID CustomerID SalesManID TotalSale
1 1 15 75
2 17 30 100
3 12 15 125
4 11 45 50
3 12 30 10
4 11 45 50

Salesman
ID Name
15 Jane Doe
30 Bob Smith
45 John Johnson
[/tt]

and you want a query to get the total amount of sales for each person and list the name:

SELECT Salesman.Name, SUM(TotalSale) As AllSales FROM
ORDER
INNER JOIN Salesman ON Salesman.ID = Order.SalesmanID
GROUP BY Salesman.Name

This will return:
[tt]
Name AllSales
Jane Doe 200
Bob Smith 110
John Johnson 100[/tt]

So, in conclusion I think you could achieve your desired results with a single query and we can help you do that. If you will fill in your tables with some sample data and the results you expect from those samples.




Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com