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

 

[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]);