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

tough query problem

P: 12
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
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Try using a subquery in the date criteria.
Jun 7 '07 #2

P: 12
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
Expert Mod 10K+
P: 12,315
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

P: 12
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
Expert Mod 10K+
P: 12,315
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

Post your reply

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