Re: Access 97 - Reports

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
dlesandrini@xxxxxxxxxxx
http://amazecreations.com/datafast


"Heather" <HWilson1@xxxxxxx> 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
>


.