By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,712 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,712 IT Pros & Developers. It's quick & easy.

Access 97 - Reports

P: n/a
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

Nov 29 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 29 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.