473,321 Members | 1,622 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,321 software developers and data experts.

tough query problem

hey all,

i am having a problem with a certain query which pulls records from two tables. one table contains info entered into a main form, and the other has information entered into the subform.

what i want the query to pull is all the records from the main table, and only the most recent record (IE latest date inputed into "date" field) from the sub table.

however what ends up happening is the query will pull all the records from the subtable. so instead of having say 5 records from the main form and 5 from the subform, i get 5 records from the main form but 20-30 from the subform!

does anyone know how to fix this
Jun 7 '07 #1
5 1466
Rabbit
12,516 Expert Mod 8TB
Try using a subquery in the date criteria.
Jun 7 '07 #2
eh thanks for the response, i have been trying to do the subquery, but i cant seem to figure it out :S do i have to create a seperate query? or just an expression in the criteria field?

thanks again.
Jun 8 '07 #3
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. SELECT x.*
  2. FROM Table1 As x
  3. WHERE x.SomeDate = (SELECT Max(SomeDate) As Expr1
  4.                     FROM Table1;)
  5. ;
  6.  
Jun 8 '07 #4
k so i used your technique and here is the code:

SELECT [Project Management].[Project Name], [Project Management].[Assigned to], [Project Management].[Implementation Start Date], [Status Updates Sub].Status, [Status Updates Sub].Date
FROM [Project Management] INNER JOIN [Status Updates Sub] ON [Project Management].ID = [Status Updates Sub].ID
WHERE ((([Status Updates Sub].Date)=(SELECT Max(Date) AS Expr1 FROM [Status Updates Sub];)))
GROUP BY [Project Management].[Project Name], [Project Management].[Assigned to], [Project Management].[Implementation Start Date], [Status Updates Sub].Status, [Status Updates Sub].Date, [Status Updates Sub].ID;

however, it is only taking the first record from the main form now. but what i need is every record from the main form and just one record from the subform (for each record in the main form)
Jun 8 '07 #5
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. SELECT x.*
  2. FROM Table1 As x
  3. WHERE x.SomeDate = (SELECT Max(SomeDate) As Expr1
  4.                     FROM Table1
  5.                     WHERE KeyField= x.KeyField;)
  6. ;
  7.  
I forgot to include a way to link the subquery to the main query.

But aside from that, why do you even have groupings? You have no need for them.
Jun 8 '07 #6

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

Similar topics

4
by: leegold2 | last post by:
Let's I do a mysql query and then I do a, for( $i = 1; $row = mysql_fetch_array($result); $i++ ) {...} and it gives me this: PageID Title URL Description 1 lee's ...
6
by: Matt Creely | last post by:
I have a very strange database with a very strange problem. Consider 4 tables: Table1: ---------------- Table1ID INT PK Table2ID INT FK Table3ID INT FK OrderNo VARCHAR(50)
3
by: Roman | last post by:
I've been trying this one for 2-3 hours and can't figure it out. I'de appreciate any help or pointers in the right direction. Thanks. Query I need the query to return me all the lottery names...
198
by: Sy Borg | last post by:
Hello: We are designing two multi-user client server applications that performs large number of transactions on database servers. On an average Application A has a 50% mix of select and...
2
by: Prince | last post by:
I have a major problem. I have an entire HTML page stored within a string variable. ex. <html><head><title>text</title></head> <body> some data </body> </html>
28
by: Arial | last post by:
My SQL string is kind of wierd one. In my application, I need to select things from an unknown name table. But I know the table name before the SQL command is executed. For instance, Dim...
12
by: Bill Bob | last post by:
I am going mad with this Query. I need to join 3 Tables. Their Formats are Vouchers NOT NULL , NOT NULL , NULL , NOT NULL , (255) CONSTRAINT PRIMARY KEY CLUSTERED
7
by: Dan | last post by:
I am trying to create a query (in either sql or the design view) to determine which two (or more I suppose if it's not too complicated) baseball players were teammates the longest. The database...
5
by: steven.fafel | last post by:
I am running 2 versions of a correlated subquery. The two version differ slightly in design but differ tremendously in performance....if anyone can answer this, you would be awesome. The "bad"...
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
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: 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: 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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.