Using Two Outer Joins To Get A Record Count With Missing Records

Hello All,

I have three tables: Employees, Gender, Diversity. Both the Gender and Diversity tables are one to many relationships with the Employees Table. I am trying to run a query that will output a count of all diversities and genders. For example:


Diversity-----Gender-----Employee Count
White----------Male---------------1
White----------Female------------5

Asian----------Male---------------3
Asian----------Female----------<Blank>

Hispanic-------Male-------------<Blank>
Hispanic-------Female----------<Blank>


I am trying to get all counts, even if the combination of diversity/gender is not in the employees table. I am going to use that information in a Crosstab query.

What I thought would work was do a Left Join For Diversity and Employee such as:

SELECT Diversity.[Diversity Description], Count(Employee.ID) AS CountOfID FROM Diversity LEFT JOIN Employee ON Diversity.ID = Employee.Diversity GROUP BY Diversity.[Diversity Description];


Then, do a Left Join for Gender and Employee such as:

SELECT Gender.[Gender Description], Count(Employee.ID) AS CountOfID
FROM Gender LEFT JOIN Employee ON Gender.[Gender ID] = Employee.Gender GROUP BY Gender.[Gender Description];

And then do a Union. But that doesn't work.

Any thoughts or comments would be much appreciated!

Thanks,

Jon