Max record

[Ngan]

I have a table called tblElig with the fields: EligID,
ClientID, EligDate, EligStatus.

A client can have a history of eligibility interviews and
they would be stored in this table. For us, we want to
see their most current eligibility. Therefore, we should
look at the Elig record of the ClientID where the EligDate
is the max. I want the current EligStatus to be displayed.

If I use a select query using aggregate (totals) function
and include the EligStatus, it will show all the records
for that client, not just the max. Here's my example:

Select ClientID, EligStatus, Max(EligDate) as MaxEligDate
From tblElig Group By ClientID, EligStatus Order by
ClientID, Max(EligDate) DESC

It would give me:
ClientID EligStatus MaxEligDate
100001 U 6/14/04
100001 I 6/1/04
100001 X 6/1/04

If I take out the EligStatus, then I would get the max
record, but it's useless since I want the EligStatus info.

Anyone have a clue to how to get the max record? I just
want the 100001, U, 6/14/04 record to be displayed.

Thanks.
Ngan

 

[Dale Fye]

Ngan,

SELECT ClientID, EligStatus, EligDate
FROM tblElig
INNER JOIN (SELECT ClientID
, MAX(EligDate) as MaxDate
FROM tblElig
GROUP BY ClientID) as MaxClient
ON tblElig.ClientID = MaxClient.ClientID
AND tblElig.EligDate = MaxClient.MaxDate

This will get you the most recent record for each of your clients.
To get a single client, just add a where clause

WHERE tblElig.ClientID = XXXXX

If all you want is the record for a single client, you could also do it
something like the following. This method is probably quicker for retrieval
of a single record, but if you want to build a recordset of all the clients
and their most recent eligibility information, the previous one will be
quicker.

SELECT ClientID, EligStatus, EligDate
FROM tblElig
WHERE ClientID = XXXXX
AND EligDate = DMAX("EligDate", "tblElig", "ClientID = XXXX")


HTH
Dale

 

[Jeff Boyce]

Consider using a two-step process.

Your first query is a Totals query that finds the Max(EligDate) for each
ClientID (GroupBy).

Your second query is based on the first, joined back to the underlying table
on the ClientID and EligDate fields (from the query's ClientID and
MaxOfCEligDate fields).

Now add the EligStatus to the output, along with ClientID and EligDate.

Warning! If a Client has more than one status on the same date, how will
you be able to tell the difference?! (a suggestion - store the date/time
value in EligDate -- the Max(EligDate) will then return the most recent date
AND TIME.)

 

[ngan]

Thanks for the replies.

I was able to do what you suggested, by using the two
queries. However, if I want to update a table with the
fields where the ClientNo equals, I get an error when I
run the update query: Operation must use an updatable
query.

Seems both two queries to find the Max Date are non-
updatable queries. So if I want to use that in an Update
query, I can't. any suggestions?

Here's a sample of what I'm trying to do:

Update tblClient LEFT JOIN qryMaxElig On
tblClient.ClientNo = qryMaxElig.ClientNo Set
tblClient.EligStatus = qryMaxElig.EligStatus,
tblClient.EligDate = qryMaxElig.EligDate

The qryMaxElig is the second query based on the first one,
joining back to the underlying table, like you suggested.

What can I do about this?

Thanks.
Ngan

 

[Jeff Boyce]

Ngan

A query would result in an un-updateable recordset if you didn't have the
primary key of the table to be updated. Check Access HELP re: Unupdateable
Query.