Query criteria

[ladybug via AccessMonster.com]

I have a table called tbl_status. In this table there are 7 fields.
status_id Autonumber
lif_id Number
status Text
sub_by Text
status_dt Date/Time
approved_dt Date/Time
notes Memo

I need to create a query that returns me the most current status that a
lif_id is in. This means that I need to know the one status with the most
current date and if there is more than one entry on that date return the
status with the higher lif_id.

I have no idea how to do this in a query. Can someone help me?

 

[John Spencer]

SELECT tbl_Status.Lif_id, tbl_status.Status_dt
, tbl_Status.Status
FROM tbl_Status INNER JOIN
(SELECT Lif_ID, Max(Status_Dt) as LastDate
FROM tbl_Status
GROUP BY Lif_ID) as Tmp
ON tbl_Status.Lif_Id = Tmp.Lif_Id
and tbl_Status.Status_dt = Tmp.LastDate

I don't understand what you mean here
"if there is more than one entry on that date return the
status with the higher lif_id. "

There may be more than one record for any one date and Lif_id, but the
Lif_ID will always be equal to itself. So this cannot be used to
discriminate to get a higher value. Did you mean to refer to another field
as the tie breaker?

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

 

[ladybug via AccessMonster.com]

What you gave me worked as far as showing me which status_id(s) has the most
recent date. However, there may be two entries entered on the same date. I
need to know which one out of the two were added last.

For example, this is what is returned with what you gave me:
status_id lif_id status_dt status
103 1390 03/25/08 open
104 1390 03/25/08 closed

I want it to only return entry 104 because it is the most recent date and it
was the last one entered for that date. Does that make sense? Sorry, if I
was unclear before. Thank you so much for your help!

SELECT tbl_Status.Lif_id, tbl_status.Status_dt
, tbl_Status.Status
FROM tbl_Status INNER JOIN
(SELECT Lif_ID, Max(Status_Dt) as LastDate
FROM tbl_Status
GROUP BY Lif_ID) as Tmp
ON tbl_Status.Lif_Id = Tmp.Lif_Id
and tbl_Status.Status_dt = Tmp.LastDate

I don't understand what you mean here
"if there is more than one entry on that date return the
status with the higher lif_id. "

There may be more than one record for any one date and Lif_id, but the
Lif_ID will always be equal to itself. So this cannot be used to
discriminate to get a higher value. Did you mean to refer to another field
as the tie breaker?

I have a table called tbl_status. In this table there are 7 fields.
status_id Autonumber

[quoted text clipped - 11 lines]

I have no idea how to do this in a query. Can someone help me?

 

[John Spencer]

SELECT tbl_Status.Lif_id
, tbl_status.Status_dt
, tbl_Status.Status
, tbl_Status.Status_ID
FROM tbl_Status
WHERE tbl_Status.Status_ID IN
(SELECT Max(Status_ID) as MaxID
FROM tbl_Status INNER JOIN
(SELECT Lif_ID, Max(Status_Dt) as LastDate
FROM tbl_Status
GROUP BY Lif_ID) as Tmp
ON tbl_Status.Lif_Id = Tmp.Lif_Id
and tbl_Status.Status_dt = Tmp.LastDate
GROUP BY tbl_Status.Lif_id, tbl_status.Status_dt)

Of course if you can always rely on Status_ID being sequential within a
group of Lif_id, then you could shorten that considerably.

SELECT *
FROM Tbl_Status
WHERE Tbl_Status in
(SELECT Max(Status_ID)
FROM tbl_Status as TMP
GROUP BY TMP.Lif_ID)

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

What you gave me worked as far as showing me which status_id(s) has the
most
recent date. However, there may be two entries entered on the same date.
I
need to know which one out of the two were added last.

For example, this is what is returned with what you gave me:
status_id lif_id status_dt status
103 1390 03/25/08 open
104 1390 03/25/08 closed

I want it to only return entry 104 because it is the most recent date and
it
was the last one entered for that date. Does that make sense? Sorry, if
I
was unclear before. Thank you so much for your help!

SELECT tbl_Status.Lif_id, tbl_status.Status_dt
, tbl_Status.Status
FROM tbl_Status INNER JOIN
(SELECT Lif_ID, Max(Status_Dt) as LastDate
FROM tbl_Status
GROUP BY Lif_ID) as Tmp
ON tbl_Status.Lif_Id = Tmp.Lif_Id
and tbl_Status.Status_dt = Tmp.LastDate

I don't understand what you mean here
"if there is more than one entry on that date return the
status with the higher lif_id. "

There may be more than one record for any one date and Lif_id, but the
Lif_ID will always be equal to itself. So this cannot be used to
discriminate to get a higher value. Did you mean to refer to another
field
as the tie breaker?

I have a table called tbl_status. In this table there are 7 fields.
status_id Autonumber

[quoted text clipped - 11 lines]

I have no idea how to do this in a query. Can someone help me?

 

[ladybug via AccessMonster.com]

THANK YOU SO MUCH!

SELECT tbl_Status.Lif_id
, tbl_status.Status_dt
, tbl_Status.Status
, tbl_Status.Status_ID
FROM tbl_Status
WHERE tbl_Status.Status_ID IN
(SELECT Max(Status_ID) as MaxID
FROM tbl_Status INNER JOIN
(SELECT Lif_ID, Max(Status_Dt) as LastDate
FROM tbl_Status
GROUP BY Lif_ID) as Tmp
ON tbl_Status.Lif_Id = Tmp.Lif_Id
and tbl_Status.Status_dt = Tmp.LastDate
GROUP BY tbl_Status.Lif_id, tbl_status.Status_dt)

Of course if you can always rely on Status_ID being sequential within a
group of Lif_id, then you could shorten that considerably.

SELECT *
FROM Tbl_Status
WHERE Tbl_Status in
(SELECT Max(Status_ID)
FROM tbl_Status as TMP
GROUP BY TMP.Lif_ID)

What you gave me worked as far as showing me which status_id(s) has the
most

[quoted text clipped - 37 lines]