Access 97 - Reports

[Heather]

Is there a way to create a report that would show all of the shifts
even if there is no data for that shift for a time period?

Shift
------
Days
Managers_Audit
Z1
Z2
Z3
Z4
Whse
TF_Sol
Maint

[Danny J. Lesandrini]

You need a cartesian join.

Let's assume you have a shifts table, tblShifts, and another table
with the data you want to aggregate, like tblEmpHours. If you do
a normal join on these tables, but have no 3rd shift data, it won't
display.

SELECT tblShift.Shift, Sum(EmpHours) As Total
FROM tblShift INNER JOIN tblEmpHours
ON tblShift.Shift = tblEmpHours.Shift
GROUP BY tblShift.Shift

However, if you remove the join, it will ...

SELECT tblShift.Shift, Sum(EmpHours) As Total
FROM tblShift, tblEmpHours
GROUP BY tblShift.Shift

My syntax might need some testing and it may vary based on your
table relationships, but what you get here by not indicating a join
is what's called a Cartesian product ... and it's what you want.

If you don't have a tblShifts, then make a qryShifts of distinct
possible values from tblEmpHours and use that in the query.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Heather" <HW******@cox.net> wrote ...

Is there a way to create a report that would show all of the shifts
even if there is no data for that shift for a time period?

Shift
------
Days
Managers_Audit
Z1
Z2
Z3
Z4
Whse
TF_Sol
Maint