How do I get just the latest record entry for each grouping by key

[bastraker via AccessMonster.com]

Ello All

OK so here's the situation. I need to generate a report of only the latest
record entry for each group of records in a query.

The records are in two tables tied together one-to-many by the key property
IssueID. For every one record in Table A there are multiple records in Table
B. The query pulls all of the fields from both tables. I need the report to
have only the most recent record for each IssueID.

Any thoughts?

 

[Marshall Barton]

OK so here's the situation. I need to generate a report of only the latest
record entry for each group of records in a query.

The records are in two tables tied together one-to-many by the key property
IssueID. For every one record in Table A there are multiple records in Table
B. The query pulls all of the fields from both tables. I need the report to
have only the most recent record for each IssueID.

Assuming you have a date/time field that can be used to
determine the "latest" record:

SELECT A.primarykey, A.fld1, A,fld2, . . .,
B.pkey, B datefld, B.f1, Bf2, . . .
FROM A INNER JOIN B
ON B.foreignkey = A.primarykey
WHERE B.datefld = (SELECT Max(X.datefld)
FROM B As X
WHERE X.pkey = B.pkey)

 

[Alan Z. Scharf]

If you already have a properly working query, join then you could use GROUP
BY.

SELECT IssueID, MAX(YourDeatField),
FROM YourQueryName
GROUP BY IssueID

Alan

 

[bastraker via AccessMonster.com]

Thanx for the input guys!! I ended up using the following.


FROM EngineeringIssues INNER JOIN EngineeringIssuesActions ON
EngineeringIssues.IssueID = EngineeringIssuesActions.IssueID
WHERE (((EngineeringIssuesActions.ActionDate)=(SELECT Max(ActionDate) FROM
EngineeringIssuesActions
WHERE IssueID = EngineeringIssues.IssueID)));