Specific Detail Table Row

[Gary B]

MASTER Table:
ClaimsPrimaryKey

DETAIL Table:
AutoNumberPrimaryKey Date ClaimsPrimaryKey ClaimsStatus

This represents, of course, a one-to-many relationship
I need to return a Query as Follows:

ClaimsPrimaryKey, ClaimsStatus

The problem is that I need to get the ClaimsStatus from the DETAIL Table,
where the DETAIL Table is Ordered By: "Date, ClaimsPrimaryKey"
and I need the info from the last row returned form this sort order.

Last Point: I am doing this on MS SQL Server if there may be an
easier/faster
way to acomplish this on that platform.

 

[Allen Browne]

Use a subquery to get the most recent status from the related table for the
record in the master table.

Something like this:

SELECT Master.*,
(SELECT TOP 1 Detail.ClaimsStatus
FROM Detail
WHERE Detail.ClaimsPrimaryKey = Master.ClaimsPrimaryKey
ORDER BY Detail.[Date] DESC, Detail.ClaimsPrimaryKey DESC)
FROM Master;

If subqueries are new, here's a starter:
http://allenbrowne.com/subquery-01.html

The results will be read-only. If you need an updatable result, you could
use ELookup() from this link:
http://allenbrowne.com/ser-42.html
The expression to type into the Field row, would be like this (one line):
ELookup("ClaimsStatus", "Detail",
"ClaimsPrimaryKey = " & Nz([ClaimsPrimaryKey],0),
"[Date] DESC, ClaimsPrimaryKey DESC")
That will be slower than the subquery.
ELookup() is like DLookup(), but with an extra argument that lets you choose
which value you want when there are multiple matches.

Also, if the field really is named "Date", you may want to rename it
something like StatusDate. DATE is a reserved word, and Access is likely to
misunderstand it in the query ("too complex" error) and in forms/report code
(where Access may think you mean today, i.e. the system date.)

When designing tables, you might want to refer to this list of field names
that can cause you grief:
http://allenbrowne.com/AppIssueBadWord.html