tough query problem

[jconstan]

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

[Rabbit]

Try using a subquery in the date criteria.

[jconstan]

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.

[Rabbit]

SELECT x.*
FROM Table1 As x
WHERE x.SomeDate = (SELECT Max(SomeDate) As Expr1
                    FROM Table1;)
;
 

[jconstan]

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)

[Rabbit]

SELECT x.*
FROM Table1 As x
WHERE x.SomeDate = (SELECT Max(SomeDate) As Expr1
                    FROM Table1
                    WHERE KeyField= x.KeyField;)
;
 

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.