wrestling with union query - it has the upper hand

I'm tracking occurances of clocking-in late, and unexcused absences in Access 
2002.  My data resides in two tables tblTardy and tblMissedShift.  I'm trying 
to automate the generation of letters of reprimand which state the total 
number of occurances.  One occurance is clocking-in late.  Another occurance 
is missing a shift with an unexcused absence -- however, missing consecutive 
days counts as ONE occurance (there is my problem).

For example:  I'm late to work Monday = one occurance.  I'm late Tuesday = 
two occurances.  I call in sick Wednesday = three occurances.  I call in sick 
Thursday = still three occurances (consecutive sick days only count as one 
episode).  I'm late Friday = four occurances.

tblTardy:
idxTardyID, intStaff, dtmClockIn, dtmStartTime

tblMissedShift:
idxMissedShiftID, intStaff, dtmClockIn, intReason

tblPersonnelData:  many fields.  The only one used here is a boolean field 
to indicate if the employee is active in our department (they are on our 
phone list).

tblStaff:  provides an indexed staff number and name.

I fumbled & blundered my way into the following UNION query:
SELECT tblStaff.chrStaffLastName, tblStaff.chrStaffFirstName, 
Count(tblTardy.dtmClockIn) AS CountOfdtmClockIn
FROM (tblStaff RIGHT JOIN tblTardy ON tblStaff.idxStaffID = 
tblTardy.intStaff) LEFT JOIN tblPersonnelData ON tblStaff.idxStaffID = 
tblPersonnelData.intStaff
GROUP BY tblStaff.chrStaffLastName, tblStaff.chrStaffFirstName, 
tblPersonnelData.bolPhoneList
HAVING (((tblPersonnelData.bolPhoneList)=True))
ORDER BY tblStaff.chrStaffLastName

UNION SELECT tblStaff.chrStaffLastName, tblStaff.chrStaffFirstName, 
Count(tblMissedShift.dtmClockIn) AS CountOfdtmClockIn
FROM (tblStaff RIGHT JOIN tblMissedShift ON tblStaff.idxStaffID = 
tblMissedShift.intStaff) LEFT JOIN tblPersonnelData ON tblStaff.idxStaffID = 
tblPersonnelData.intStaff
GROUP BY tblStaff.chrStaffLastName, tblStaff.chrStaffFirstName, 
tblPersonnelData.bolPhoneList
HAVING (((tblPersonnelData.bolPhoneList)=True));

This query lists a name with the total number of entries, for that person, 
in the table.  It's close, but not quite what I need.  Any solutions?  Need 
any more info?? Be gentle, I'm not an IT pro.  

Thanks,
Michael Mac

Well if you turned up to work once in a while, then you might already have
it figured out.  (Sorry, can't resist the joke.)

What data are you storing regarding missed shifts?  One record for the
Wednesday, and one for the Thursday, or one record that contains a start and
end date?






Access
trying
occurance
consecutive
sick
=
Need