MASTER -> DETAIL {the last row entered ordered by Date then PrimaryKey}

[Gary B]

MASTER Table:
ClaimsPrimaryKey

DETAIL Table:
AutoNumberPrimaryKey Date ClaimsPrimaryKey ClaimsStatus

This represents, of course, a one-to-many relationship

I need a Query to return the following:

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]

The quickest approach would be to use a subquery, e.g.:
SELECT Master.*,
(SELECT TOP 1 ClaimsStatus
FROM Detail
WHERE Detail.ClaimsPrimaryKey = Master.ClaimsPrimaryKey
ORDER BY Detail.[Date] DESC, Detail.AutoNumberPrimaryKey DESC)
AS CurrentStatus
FROM Master;

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

That will give a read-only result set. The alternative would be to use a
function like DLookup() to get the value. However, DLookup() is not powerful
enough so try something like this ELookup() replacment:
http://allenbrowne.com/ser-42.html

The query would use the Master table only, and you would type an expression
like this into the Field row (as one line):
ELookup("ClaimsStatus", "Detail",
"ClaimsPrimaryKey = " & Nz(Master.ClaimsPrimaryKey, 0),
"[Date] DESC, AutoNumberPrimaryKey DESC")

BTW, Date is a reserved word in SQL (both JET and SQL Server), and will be
misunderstood for today's date in VBA code. It would therefore be a good
idea to rename to field to something like ClaimStatusDate. Here's a list of
the names to avoid when desiging your tables:
http://allenbrowne.com/AppIssueBadWord.html

 

[Gary B]

Allen,

By "quickest" do you mean:

1.) The quickest way to solve it?
2.) The fastest/most efficient query result?

This is important to know cause it is actually a part of a bigger query
against 2 million records.

Others were advising A Master Query joining on a erived table that joins on
yet another derived table.

Thank you so much for your help!

 

[Allen Browne]

I meant faster executing.

I should have said "quicker" of the 2 methods I suggested.

For 2m records, it may be possible to get a better result using the query
others suggested. Here's some more info:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

 

[John Spencer]

How about using the following query? I think it may be the fastest
executable. You can test this one against the other options you have
been given and see which is fastest.

SELECT ClaimsPrimaryKey, ClaimsStatus, [Date]
FROM Details as D INNER JOIN
(SELECT ClaimsPrimaryKey, Max(DA.Date) as MaxDate
FROM Details as DA
GROUP BY ClaimsPrimaryKey) as DB
ON D.ClaimsPrimaryKey = DB.ClaimsPrimaryKey
AND D.[Date] = DB.[Date]

You may have trouble with this query since you named a field Date. Date
is a reserved word in Access.

You should be able to run the same query in TSQL on an SQL server. If
you want more than just the three fields mentioned, you can add your
Master table into the query also.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Gary B]

Allen,

Thanks so much.

Let me ask you...

If I need to do a Max for first the date then the primary key {which is an
autonumber that skips by one}, is is possible to combine the integer
primarykey + the datetime ClaimsDate Field into one Max()

Select ClaimID, Max(ClaimsDate + PrimaryKey)
From Detail
Group By ClaimID

1.) Also, are the solutions the same for both Access and SQL Server?
2.) Is there a newsgroup {blog} that you know of that is specific to Queries
in MS SQL Server

You're the best, thanks again!

 

[Allen Browne]

Perhaps something like this:
SELECT ClaimID, DetailID, ClaimsDate
FROM Detail
WHERE DetailID =
(SELECT TOP 1 Dupe.DetailID
FROM Detail AS Dupe
WHERE Dupe.ClaimID = Detail.ClaimID
ORDER BY Dupe.ClaimsDate DESC,
Dupe.DetailID DESC);

For 2m records, it might be worth creating a temp table to hold just the
primary key value from the detail table for the most recent detail record
for each ClaimID. It should then be very efficient to JOIN this with the
other 2 tables, and get all the values from the most recent related record.