Query or Report not sure where to go.

[TPattison]

Hi all, I am not very versed in Access, but I know enough to be dangerous. Here is what I am doing. I have MS Access 2010.

I have the main table that has the following fields: Employee ID, Employee Gender, Employee Race, Hire Date, Termination Date

The second table that links to each employee record has: Employee ID (to link to the employee record), Job Title, Date Job Title was Effective

Each employee record may have multiple Job Titles.

What I want to do is create a report that captures the data in the following way:

Job Title................White Male.....Black Male.....Asian Male.....While Female etc.
Widget Maker...............1.................3..................5..................2
Widget Inspect.............3.................6..................2..................4
etc.

How do I do this? Is it multiple queries, or can it be one query?

Thanks, I've been beating my head against the wall on this :banghead:.

 

[recyan]

Just check out if below gives some guidelines :

The sub-query to be saved :

qryRaceGenderTitle_TitleCount

SELECT 
	tblEmpJobTitles.JobTitle, 
	[EmployeeRace] & " " & [EmployeeGender] AS EmpRaceGender, 
	Count(tblEmpJobTitles.JobTitle) AS CountOfJobTitle
FROM 
	tblEmployees 
	RIGHT JOIN 
	tblEmpJobTitles 
	ON 
	tblEmployees.EmployeeID = tblEmpJobTitles.EmpID
GROUP BY 
	tblEmpJobTitles.JobTitle, 
	[EmployeeRace] & " " & [EmployeeGender];

The cross-tab query to run for the data presentation :

TRANSFORM First(qryRaceGenderTitle_TitleCount.CountOfJobTitle) AS FirstOfCountOfJobTitle
SELECT 
	qryRaceGenderTitle_TitleCount.JobTitle
FROM 
	qryRaceGenderTitle_TitleCount
GROUP BY 
	qryRaceGenderTitle_TitleCount.JobTitle
PIVOT 
	qryRaceGenderTitle_TitleCount.EmpRaceGender;

Thanks