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.
Expand|Select|Wrap|Line Numbers
- 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;
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.