473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Having a problem limiting results of query

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.

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
  2.  
  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
  4.  
  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))
  6.  
  7. ORDER BY Eng_Phase.Phs_MainPrj, Eng_Rate.RT_DtFrm DESC;
  8.  
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
3 1398
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 :)
Oct 10 '07 #2
nico5038
3,080 Expert 2GB
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)
Oct 10 '07 #3
thanks Nico
I'll give it a try when I get a chance.....it might speed up the query a bit.
Oct 11 '07 #4

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

Similar topics

6
by: Kingdom | last post by:
I'm using this script to dynamicaly populate 2 dropdowns and dispaly the results. Choose a component type from the first drop down, lets say 'car' and the second box will list all the car...
1
by: George | last post by:
Hi, I am trying to write a query in Oracle which I have not done before, and are having some difficulty getting my result. Please check my query and my results. select max(note.datetime),...
1
by: sks | last post by:
Hi, I have a products table which apart from other things has a column called manufacturer. I want to run a query that returns x number of products PER manufacturer for each manufacturer that...
25
by: Tom Leylan | last post by:
Help :-) I can't see how this should be laid out! Consider the scenario of a car rental company. They have "cars" and "lots" (nationwide) and "customers." So far it is easy to query each of...
1
by: k k | last post by:
Hi, The following query has failed to return all the records. SELECT b.Account_desc, b.Account, IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01', a.CSPL_CMS,0),...
20
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
2
by: mpar612 | last post by:
Hi everyone, The code posted below retrieves all of the rows from a database table. It paginates them by limiting to 20 results per page. It is supposed to print 5 rows of results with 4...
15
by: Hexman | last post by:
Hello All, How do I limit the number of detail records selected in a Master-Detail set using SQL? I want to select all master records for a date, but only the first 3 records for the details...
1
by: JustinCarmony | last post by:
I'm using a DataAdapter to fill a DataSet to display a list of items in a custom way. I'm not using the GridView or any server controls like that. This is my code: <code> SqlConnection sqlConn...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.