Having problems . . . help?

[Guest_imported]

ok, yesterday I left a brief question, thinking short was best. Well, the answer I got was "not enough info."
So . . . I am working on a project. One aspect of which is tracking errors. We will enter the employee errors into a table. From this table and one other, I plan to make a cross tab with the employees listed across the left and the months of the year across the top. Now, we have an incentive program for not making any errors with in a one month, three month, six month and one year time frame. By my reasoning, and the way I intend to create the cross tab, any month without an error would be a zero or null value. I would like to start with the month just past and begin counting consecutive months (going back to January) with out errors. As soon as the program reaches a 1, 2 or whatever it stops counting consecutive months and goes to the next employee as this number would mean the employee made an error. It would then return the total consecutive months for the employee which I could use in another query and/or report.

So, to sum up. employees listed on left, months on top (Jan, Feb. . .). Program begins in month just past (awards given after month is over) and count back until it finds a month with an error and stops counting. Results could be anywhere from 0 to 12. The results I'll then feed into further queries and reports. Hopefully, this will save me and others a lot of work (and needless thought) when complete. Any answers/solutions? Please help. I am trying quite diligently to learn this whole thing, but I am just starting.

Thanks,

CS

Feeling ambitious? When I run this thing in January I need it to return the values for the previous year. I feel hopeless. Am I biting off too much? Thanks for listening and helping if you can.

 

[gcole]

What you want to do is pretty complicated but possible. This may be a project for you IS people.

 

[MichaelRed]

Well, just to get started down the primrose path ... (You know it - paved w/ gold leading to ???)


A sample [EmpErr] Table

EmpId ErrCode ErrDt
24468234 0 10/15/00
261647386 1 5/1/00
261647386 3 6/7/00
261647386 4 7/8/00
5314197 6 4/23/00
24468234 12 6/6/00
5314197 16 4/29/99
261647386 19 7/7/00
24468234 22 6/8/00
5314197 26 6/16/00
24468234 32 7/10/00
5314197 36 6/18/00
24468234 42 10/11/00
5314197 46 10/20/00
261647386 54 11/15/00

A sample [Mnths] table. Note that Mnth is in the table, but is blank!
EmpId ErrCode ErrDt Mnth
-9999999 -32767 1/1/00
-9999999 -32767 2/1/00
-9999999 -32767 3/1/00
-9999999 -32767 4/1/00
-9999999 -32767 5/1/00
-9999999 -32767 6/1/00
-9999999 -32767 7/1/00
-9999999 -32767 8/1/00
-9999999 -32767 9/1/00
-9999999 -32767 10/1/00
-9999999 -32767 11/1/00
-9999999 -32767 12/1/00

the query to get the [Mnth] filled in for the XTab A.K.A. [qrympErrByMnthForCurrYr]

SELECT tblEmpErrs.EmpId, tblEmpErrs.ErrCode, tblEmpErrs.ErrDt, Month([ErrDt]) AS Mnth
FROM tblEmpErrs
WHERE (((tblEmpErrs.ErrDt)>=DateValue("1/1/" & Year(Now()))))
Union Select tblMnths.EmpId, tblMnths.ErrCode, tblMnths.ErrDt, Month([ErrDt]) AS Mnth
From tblMnths;


The actual Crosstab Query
TRANSFORM Count(qrympErrByMnthForCurrYr.ErrCode) AS CountOfErrCode
SELECT qrympErrByMnthForCurrYr.EmpId
FROM qrympErrByMnthForCurrYr
GROUP BY qrympErrByMnthForCurrYr.EmpId
PIVOT qrympErrByMnthForCurrYr.Mnth;


Given that all of your table and field names will be different, you need to revise the queries to reflect your nomencalture.

No real tricks here, except (possibly) the inclusion of [tblMnths]. This "assures" that the crosstab will have a column for each Month of the year, at the expense of introducing a bogus employee who has the same bad error code each month. Note that is not necessary to update the [Mnth] column for this tabls, as we only really care that we extract the month for all months (1 : 12) here.

Another slightly "off-beat" approach is the "Union" part of [qrympErrByMnthForCurrYr]. This means that this cannot be fiddled with in the query builder, so all "edits" have to be done in the "SQL" view. An 'easy' way to approach this is to copy/paste the supplied query into a query builder window with the SQL view selected. Then, you can add/delet/edit the query with cut/copy/paste of table and field names. Rember that IF you modify the fields of the "errors" table, you need to make the same changes to tblMnths, although you do not - in general - need to fill these in w/ data.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over