Query Not Returning Zero Counts

[TimMoffy]

I have a number of queries that report on how many widgets a member of the team has actioned in a given time frame.
The problem is that each person rotates their work around so that they work on a different line of business at several points in the week and sometimes will not do anything on line of business, LOB number 1 for example in a given time frame.

When i run the queries to show productivity, if a given member of the team has done zero work on that line of business, then they do not appear in the report, which in itself is not a disaster as the Team Manager should know which work they were assigned to and should expect them to be missing from reports.
Not ideal but we can live with it.

However as different levels of mgt need different reports i then take these queries, four of them in total, and join them to give a single summary view of all activity for the week for more senior mgt. Again this works well except where someone has a zero return in any one of the queries, in which case they are missing entirely for the summary report query, even for lines of business where they have produced work and where they do show up in the individual activity reports.



I guess this comes down to the fact that they are missing from a single query and this affects the whole of the summary query, which is four queries joined together by staff ID.
My question therefore is, " how can i ensure that staff who have not done any work in a given period of time, on a given line of business, appear as a zero return in the queries.
The queries are very simple
Three columns:

Group by Team Member ID,
Count by Team Member ID,
Dates Between #[Start]# AND #[End]#

I can only think of doing a cross tab query, but that means changing so many other objects as well.

Thanks as always for your help.

[recyan]

Just thinking out aloud
1) There are 4 queries joined together on StaffID.
2) Each query might return some or all StaffID.
3) A StaffID might be in one but not in another.
4) Perhaps we could have another query which fetches all the distinct StaffID from the Staff Table & use this as the Left Table with a Left Join on the other queries on StaffID. But this will also bring up those Staff who are not there in any of the 4 queries.
5) perhaps, we could have a union query on all the 4 queries with StaffID as the field, to get distinct StaffID in a query & then use it in the same manner as outlined in 4).
6) Nz()

Thanks

[TimMoffy]

got to point 4 and tried that, choosing option 2 from the join properties and guess what.............several empty fields are now showing on the report, which is exactly what we wanted - Thanks

[recyan]

Glad you found the loud thinking helpful.

Thanks