How to calculate a total of "0" when there is no data

[wlanssens]

Hi,

I'm trying to figure something out but it came to a point where I need some assistence because its getting hair pulling frustrating.

Say I have a table with ID's of people and a column with the hours they need to work on a specific day.
I want to make a query where I get the total working hours for every person in that table for a certain date.
The problem is that when a specific person in the table has no hours for a certain day he doesn't show up in the query if I choose that day.., is there a way to make sure that I get everybody's names for every day even if they don't have any hours on that day, but instead of not showing it would show a "0" instead?

thanks!

[June7]

That requires a dataset of all possible people and date combinations. Try something like:

Query1 - create a dataset of all dates in work hours table
SELECT DISTINCT WorkDate FROM HoursTable;

Query2 - create a dataset of all possible employee/date combinations by omitting join clause which produces a cartesian relation of records
SELECT EmployeeID, WorkDate FROM Employees, Query1;

Query3 - basically a find unmatched query and the query wizard can help with this
SELECT Query2.*, HoursTable.*, Nz(WorkHours,0) AS HoursWorked FROM Query2 RIGHT JOIN HoursTable ON Query2.EmployeeID=HoursTable.EmployeeID AND Query2.WorkDate=HoursTable.WorkDate;

I might be wrong about the RIGHT and maybe should be LEFT - always confuses me - use query builder. Want the join to 'Include all records from Query2 and only those from HoursTable that match'.