Query advise please

[John]

Hi

I have two tables

1. tblJobs with JobID and Date fields for all jobs booked, and

2. tblStaff with JobID and StaffID for staff booked on a job.

Link between the two tables is JobID.

My question is how can I return staff counts for jobs of this year and staff
count for last year broken down by months. Like below;

This Year Staff Count Last Year Staff Count

Jan
Feb
Mar

....etc.

Any help would be appreciated.

Many Thanks

Regards

 

[Steve]

Three queries would do it.
1. Totals query that gives you Staff Count per job for this year
2. Totals query that gives you Staff Count per job for last year
3. Crosstab query that includes 1 and 2

Steve

 

[John]

So if I have the following queries for each year's count how do I crosstab
them?

Thanks

Regards

This year's count

SELECT Month(tblJobs.[Date]) AS M, Count(tblStaff.[Staff ID]) AS
[CountOfStaff ID]
FROM tblJobs INNER JOIN tblStaff ON tblJobs.JobID = tblStaff.JobID
WHERE (((Year(tblJobs.[Date]))=Year(Date())))
GROUP BY Month(tblJobs.[Date]);

and

Last year's count

SELECT Month(tblJobs.[Date]) AS M, Count(tblStaff.[Staff ID]) AS
[CountOfStaff ID]
FROM tblJobs INNER JOIN tblStaff ON tblJobs.JobID = tblStaff.JobID
WHERE (((Year(tblJobs.[Date]))=Year(Date()-365)))
GROUP BY Month(tblJobs.[Date]);