By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Having a problem limiting results of query

P: 11
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
  1. 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
  3. 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
  5. 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))
  7. 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.
Oct 10 '07 #1
Share this Question
Share on Google+
3 Replies

P: 11
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 works :)
Oct 10 '07 #2

Expert 2.5K+
P: 3,072
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.

Oct 10 '07 #3

P: 11
thanks Nico
I'll give it a try when I get a might speed up the query a bit.
Oct 11 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.