Having a problem limiting results of query

[DDonnachie]

Hi,
I want my query to go through all the employee hours and list the associated rates for each record. This means going through Eng_Costing and linking to Eng_PrjGrds (matching Project and Grade), then linking to Eng_Rate using Rate ID.

SELECT Eng_Phase.Phs_MainPrj, Eng_Costing.CST_Grade, Eng_Costing.CST_Date, Eng_Costing.CST_Norm_H, Eng_Costing.CST_OT_H, Eng_Costing.CST_OTX_H, Eng_Rate.RT_Norm, Eng_Rate.RT_OT, Eng_Rate.RT_OTX
 
FROM Eng_Rate INNER JOIN Eng_PrjGrds ON Eng_Rate.RT_ID = Eng_PrjGrds.PRJGRD_RateID, Eng_Costing INNER JOIN Eng_Phase ON Eng_Costing.CST_Phase = Eng_Phase.Phs_Auto
 
WHERE (((Eng_Rate.RT_DtFrm)<=[eng_costing].[cst_date]) AND ((Eng_PrjGrds.PRJGRD_Prj)=[eng_phase].[phs_mainprj]) AND ((Eng_PrjGrds.PRJGRD_Grade)=[eng_costing].[cst_grade]) AND ((Eng_Costing.CST_OverHead)=False))
 
ORDER BY Eng_Phase.Phs_MainPrj, Eng_Rate.RT_DtFrm DESC;
 

My problem is this, Eng_Costing has around 3500 records and the query returns about 6000. It is caused by the value RateID having duplicate entries in the Eng_Rate table, these duplicate entries are necessary to allow the continuous updating of Rates charged to customers.
I need the query to only return one Eng_Rate record for each Eng_Costing record, the Eng_Rate record I want is the first one found.

If anyone has any ideas...please feel free to suggest them.
I thought about perhaps using 'Distinct' or 'Top 1', but obviously either i'm putting them in the wrong place or they just aren't suitable for this.

I'm using Access 2003 on XP.

[DDonnachie]

Nevermind folks....solved

I changed the query to call a function which locates the rate for each record, not quite what I was originally hoping for but.....it works :)

[nico5038]

For the requirement:
"I need the query to only return one Eng_Rate record for each Eng_Costing record, the Eng_Rate record I want is the first one found."
You could have used a GroupBy query with the "First" option for the value needed.

Nic;o)

[DDonnachie]

thanks Nico
I'll give it a try when I get a chance.....it might speed up the query a bit.