How to show records that don't exist

  • Thread starter Thread starter Jacques
  • Start date Start date
J

Jacques

I have a query that shows me JobTitles, OfficeBranch, and count of users
(counting social's).

There are 4 OfficeBranches. I need the query to list all four
OfficeBranches even when there are no corresponding JobTitles and users.
Since the query is counting users, the report needs to show that there are
zero users for that JobTitle in that branch office when there are none.

Note, that I can't use a join in the query properties, because this query
pulls from another, and the 4 OfficeBranches are just a valuelist for a
lookup field, and not in a seperate table.

I know how to make a CrossTab Query show all the fields, but how to make a
field have data that isn't there for the report to do math against it
(counts, percentages, etc).

Any help is appreciated. If I'm not being clear, just ask and I'll restate
it.

Thanks,
 
Replace your lookup field with a separate table of office branches. Use this
lookup table in the Row Source of combo box on forms. You can then create a
cartesian query that combines all office branches with all JobTitles.
 
Duane,
That worked out well. I did it in a different manner but you've clearly
taught me a new trick.
 
Back
Top