Re: Query criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

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!

John Spencer wrote:
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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200803/1

.

Relevant Pages

  • Re: VBA Macro help needed
    ... When same entry based on Customer Id is typed ... sheet 2. ... when duplicate record was found, the record should be ignored or should ... If Not tmp Is Nothing Then ...
    (microsoft.public.excel.programming)
  • Re: Query criteria
    ... as LastDate ... GROUP BY Lif_ID) as Tmp ... current date and if there is more than one entry on that date return the ... I have no idea how to do this in a query. ...
    (microsoft.public.access.queries)
  • RE: Lookup function
    ... showing it. ... I used lookup function. ... How can I fix the problem? ... If my entry does not exist in the table, ...
    (microsoft.public.excel.worksheet.functions)
  • Search on field in subform
    ... If I search on a field in a subform it only searches ... records associated with the entry currently showing on the ... contents of the record set. ...
    (microsoft.public.access.formscoding)
  • Re: PTR record is not changing
    ... I did flushdns yesterday, but still it is showing old entry when i check ... using various web sites like ...
    (microsoft.public.windows.server.dns)