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

Jonas,
How are the 2 tables indexed?

Kevin

dartcoach

hey kevin,

here is the code that is on the query, as of right now it is pulling the correct record from the subform, however it is only taking it from the first main form record. my report has one line basically, but should have more :S

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;

Jonas,

Is there always a record in the sub table?

Kevin

dartcoach

yes, i set the date to =Date() as the default value

Try reversing your logic. Get the last updated record for the sub table, then match to the main table.

Kevin

dartcoach

I'm still having a little trouble. I want to pull the most recent date from the "status subform" pulls the project "name" from its main form. i will illustrate below what i would like to see:

Before Query:

Date Name
1/1/07 A
1/2/07 A
1/2/07 B
1/2/07 C
1/3/07 A
1/4/07 B

After Query:

Date Field
1/3/07 A
1/4/07 B
1/2/07 C

I tried using Max(Date) to get the most recent date, however it only pulls "1/4/07 B" which is the latest of ALL the dates, not just he "B" dates.

i tried matching it to the main table as well but that isn't working either :S

Jonas,
Date comes from the sub table and Name comes from the main table?

Kevin

dartcoach

yes, thats correct

Which 1 is on both?

dartcoach