wrestling with union query - it has the upper hand

G

Guest

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
 
R

Rob Oldfield

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?
 
G

Guest

Missed shift info goes into tblMissedShift:
idxMissedShiftID(autonumber field), intStaff (identifies staff member from
tblStaff), dtmClockIn (date AND time of missed shift), intReason (identified
reason from tblReason - sick, bereavment leave, etc.).

Each record holds info on one missed shift.

I hope you can pull a rabbit out of the hat for me. Thanks.
 
R

Rob Oldfield

Hmmm. Certainly not straightforward but I think I'm getting somewhere. But
one other question that I need to ask: if there's a weekend in the middle of
two missed shifts, then does that still count as one? Similarly, do you
have to take account of holidays? If someone misses a shift on Friday, the
Monday is a holiday, and then also misses Tuesday, is that also still just
one?
 
G

Guest

It is a 24/7 business. As far as our employer is concerned there is no such
thing as a holiday, or weekend. All days are the same. A little depressing
now that I think about it...pass the prozac, better living through chemistry.

Michael
 
R

Rob Oldfield

In that case I think I have it.

Counting tblTardy is obviously straightforward - just a straight group
query. It's tblMissedShift that is the troublesome one. So.. on that...

Query M1 - just uses the DateValue function to strip out the time part of
the dtmClockIn. You could also use it to filter out and intReasons that
don't count for whatever reason.

SELECT tblMissedShift.intStaff, DateValue([dtmClockIn]) AS dt,
tblMissedShift.idxMissedShiftID
FROM tblMissedShift;

Query M2 - the tricky bit. A self join that only returns records which have
a corresponding record for the same person, but the day before. In effect,
a list of records which don't need to be included (because the person was
off the day before).

SELECT M1_1.idxMissedShiftID, M1_1.intStaff
FROM M1 INNER JOIN M1 AS M1_1 ON M1.intStaff = M1_1.intStaff
WHERE (((DateDiff("d",[m1].[dt],[m1_1].[dt]))=1));

Query M3 - then just a standard subtract query to remove those records that
appear in M2 from the original list. The date returned will be the first
shift in a set of absences.

SELECT tblMissedShift.idxMissedShiftID, tblMissedShift.intStaff,
tblMissedShift.dtmClockIn
FROM M2 RIGHT JOIN tblMissedShift ON M2.idxMissedShiftID =
tblMissedShift.idxMissedShiftID
WHERE (((M2.idxMissedShiftID) Is Null));


From there, you can work out a total amount from the two types in various
ways.
 
G

Guest

You did it!! Thank you so much. I beat my head against the wall for weeks
trying to figure this out. Hats off.

You are either an Access/SQL genius, or possibly just twisted enough to
figure this out.

Rob Oldfield said:
In that case I think I have it.

Counting tblTardy is obviously straightforward - just a straight group
query. It's tblMissedShift that is the troublesome one. So.. on that...

Query M1 - just uses the DateValue function to strip out the time part of
the dtmClockIn. You could also use it to filter out and intReasons that
don't count for whatever reason.

SELECT tblMissedShift.intStaff, DateValue([dtmClockIn]) AS dt,
tblMissedShift.idxMissedShiftID
FROM tblMissedShift;

Query M2 - the tricky bit. A self join that only returns records which have
a corresponding record for the same person, but the day before. In effect,
a list of records which don't need to be included (because the person was
off the day before).

SELECT M1_1.idxMissedShiftID, M1_1.intStaff
FROM M1 INNER JOIN M1 AS M1_1 ON M1.intStaff = M1_1.intStaff
WHERE (((DateDiff("d",[m1].[dt],[m1_1].[dt]))=1));

Query M3 - then just a standard subtract query to remove those records that
appear in M2 from the original list. The date returned will be the first
shift in a set of absences.

SELECT tblMissedShift.idxMissedShiftID, tblMissedShift.intStaff,
tblMissedShift.dtmClockIn
FROM M2 RIGHT JOIN tblMissedShift ON M2.idxMissedShiftID =
tblMissedShift.idxMissedShiftID
WHERE (((M2.idxMissedShiftID) Is Null));


From there, you can work out a total amount from the two types in various
ways.


Michael Mac said:
It is a 24/7 business. As far as our employer is concerned there is no such
thing as a holiday, or weekend. All days are the same. A little depressing
now that I think about it...pass the prozac, better living through chemistry.

Michael
Click to expand...
Click to expand...
 
Top