Hi, I have the following tables:
Technician {TechnicianID#, Name, Surname, Address...}
Job {JobID#, JobDate, ArrivalTime, DepartureTime, Postcode...}
Job_Recovery {TechnicianID#, JobID#}
the last table is an intermediate table. one job is done by two technicians. I'm writing a query to find who a particular technician (TechnicianID) worked with in the past month. The ideal result for TechnicianID=T001 would be displayed like this: (totally random values)
Technician JobCount
T002 2
T003 5
T010 3
I got this:
SELECT JobRecovery.TechnicianID,
COUNT ([JobRecovery].[JobID]) AS JobCount
FROM JobRecovery LEFT JOIN Job ON Job.JobID=JobRecovery.JobID
WHERE Job.JobDate >= ((Date())-30)
GROUP BY TechnicianID;
The JobCount bit seems right and i think the job date is alright as well. I can't get the Technician part right - it displays all technicians including TechnicianID='001' and adding TechnicianID='001' after the WHERE is no good either - the table only displays technician 001.
Could someone tell me where I am going wrong please?