Query to bring zero hours reported into result set

[jakeman]

Hello - I have a query dilemma. I pull employee time out of a database each week that tells me how many hours an employee worked. The problem with the database is that it doesn't tell me which employees failed to report time, it only tells me which employees reported time.

Now, using an employee census table, I compare the time results by employee against my employee census table to figure out which employees failed to report any time. But this process is kind of labor intensive and somewhat error prone.

I've been trying to tinker around with creating a query that will give me the results of those folks who reported time and for those folks who didn't report time (by displaying zeros), but I cannot get this to work at all. I created a third table which is a calendar table and lists all of the workdays for the year. In my mind, I think there must be a way to combine the time results table (common fields are employee ID and date reported), census table (common field is employee ID), and the calendar table (common field is date).



Is there a way to create a query that aggregates actual time reported for each employee by date with time not reported by employee by date? Basically, if an employee failed to report time, I'd like to know the date and in the time reported field, list a zero. Maybe this has to happen in two or more steps?

Thanks.

[June7]

You have a master table of all employees? Create query that joins data to the master, jointype 'Include all records from Employees ...'