Query combine 2 tables check for omitted employee

[GaryAks]

A query to combine 2 tables to show omitted employees on a given day

A table of tasks done

Date Empl Hrs WorkDone
2/3/03 Joe 5.5 Wigit 1
2/3/03 Joe 2.5 Wigit 2
2/3/03 Fred 5 Wigit 9
2/3/03 Fred 2.5 Wigit 1
2/3/03 Bill 4 Wigit 4
2/3/03 Bill 3.5 Wigit 7
2/3/03 John 3 Wigit 1
2/3/03 John 4.5 Wigit 7
Jack and Paul are missing on this day

A table of all possible employees

Joe
Fred
Bill
Jack
John
Paul

Needed a query that will combine both tables showing total hrs for employees who worked, and no hrs for omitted employees for a given day, I have not been able to have the omitted employees show with no hrs for each date in the "tasks done" table. Is this possible?

2/3/03 Bill 7.5
2/3/03 Fred 7.5
2/3/03 Jack
2/3/03 Joe 8.0
2/3/03 John 7.5
2/3/03 Paul

 

[luceze]

It looks like you have a one to many relationship between your employee table and your tasks done table.

In the query builder, select both tables. Create a link between the unique identifier in your Emp table and the emp identifer in your tasks table. Set the link to select all records from the employee table and those that are equal in the tasks table.
In your query fields add your employee from the employee table and the date and hours from your tasks table.
Click the sigma button to bring up the total line and select sum for the hours field.

That should do it!
If you need more info post back.

HTH,
Eric

 

[GaryAks]

Eric
Thanks for your help, I did what you suggested before I posted the original question. The problem with this query the employees that are not in the "task done" table do not show up in the final query. I have done as you suggested and "Set the link to select all records from the employee table". The result is the employees in the "task done" tables hours are properly summed, but the missing employees for that particular day are not shown. The main problem is gettting the missing employees to show with 0 hrs.
Thanks again, but still have the above issue.
Gary