Need help with query looking at two tables

[DougP]

both tables have Project number in common.
One table is the AirMonitortable the other one is Contractor Staff...
Examle: there are 4 projects with the same number 070123
and the AirMonitor table. There are 5 Techs assigned to that job 070123. The query is returning 20 records, each of the five ContractorTechName is repeated 4 times for each of 4 AirMonitor records. How can I change the query to return just the 5 ContractorTechName 's?

AirMonitor table
[Project number] [Area]
070123 1
070123 2
070123 3
070123 4


Contractor Staff... table
[Project number] [ContractorTechName]
070123 Fred
070123 Sue
070123 Sally
070123 Jim
070123 John

here is my query
SELECT AirMonitor.[Project Number], [Contractor Staff Qualifications n Licenses].Num, [Contractor Staff Qualifications n Licenses].ContractorTechName, [Contractor Staff Qualifications n Licenses].SS, [Contractor Staff Qualifications n Licenses].DateOfTraining, [Contractor Staff Qualifications n Licenses].TrainingCompany, [Contractor Staff Qualifications n Licenses].OriginalTraining, [Contractor Staff Qualifications n Licenses].RefresherTraining, [Contractor Staff Qualifications n Licenses].ABPhysical, [Contractor Staff Qualifications n Licenses].ABPhysicalDateExpire, [Contractor Staff Qualifications n Licenses].FitTest, [Contractor Staff Qualifications n Licenses].FitTestDateExpire, [Contractor Staff Qualifications n Licenses].WorkAcknowlegement, [Contractor Staff Qualifications n Licenses].Notes, AirMonitor.Facility, AirMonitor.[Project Monitor], AirMonitor.Contractor, AirMonitor.DateAssigned
FROM AirMonitor INNER JOIN [Contractor Staff Qualifications n Licenses] ON AirMonitor.[Project Number] = [Contractor Staff Qualifications n Licenses].[Project Number]
WHERE (((AirMonitor.[Project Number])=[Forms]![frmAirMonitor]![Project Number]));

Hope you can understand this.
TIA



DougP, MCP, A+

I love this site and all you folks that helped me over the years!

 

[PHV]

How can I change the query to return just the 5 ContractorTechName
Don't join the AirMonitor table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

or decide which of the records from AirMonitor you want to get the information from. If Facility, Project Monitor, Contractor and DateAssigned are the same for Areas 1 - 4 then DISTINCT should work. If those fields are different for each of those records you'll need to decide which one to use.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins