Records Selection Query (Last or Max)

[Tamrak]

Good morning,

I have a question that might look simple but I cannot figure it out. My actual table has over 25 fields.

I will pick up six (6) fields toward this example, of 23 records – Flat File –

Table Name: Project_Info

Project, Status_Date, Activity_Date, Status, ChangedInfo, Changed_by

100, 1/26/2006, 1/27/2006, Requested, Project has been requested, Peter
100, 1/26/2006, 1/31/2006, Requested, Project has not yet received, Mike
100, 1/26/2006, 2/1/2006, Requested, Project has received, Mary
100, 2/7/2006, 2/8/2006, Approved, Project is approved, Mary
100, 2/15/2006, 2/15/2006, Active, Project starts, Paul
100, 3/15/2006, 3/15/2006, Hold, Project was put on hold, Paul
121, 2/11/2006, 2/11/2006, Requested, Project has been requested, Peter
121, 2/16/2006, 2/16/2006, Approved, Project has been approved, Mary
133, 2/25/2006, 2/25/2006, Requested, Project has been requested, Peter
133, 3/1/2006, 3/2/2006, Approved, Project is approved, Mary
133, 3/1/2006, 3/3/2006, Approved, Project information added, Mary
133, 3/5/2006, 3/5/2006, Active, Project is turning active and starts, Paul
133, 3/15/2006, 3/15.2006, Cancelled, Project is cancelled due to financial issue, Clark
133, 3/16/2006, 3/16/2006, Cancelled, Received official cancellation notification, Clark
144, 2/16/2006, 2/17/2006, Requested, Project has been requested, Peter
144, 2/19/2006, 2/19/2006, Approved, Project has been approved, Mary
144, 2/19/2006, 2/22/2006, Approved, Still waiting for the paperwork, Mary
156, 3/25/2006, 3/25/2006, Requested, Project has been requested, Peter
156, 5/15/2006, 5/15.2006, Completed, Project is completed, Clark
156, 5/31/2006, 5/31/2006, Billed, Sent the bill for invoicing, Clark
156, 3/25/2006, 3/25/2006, Requested, Project has been requested, Peter
156, 4/1/2006, 4/2/2006, Approved, Project is approved, Mary
156, 4/1/2006, 4/3/2006, Approved, Project information added, Mary
156, 4/5/2006, 4/5/2006, Active, Project is turning active and starts, Paul
…..

I receive this unsorted flat file every day because the records are appended on the daily basis. The table is not sorted. For example, project # 156 has the second record as completed while the later records showed the beginning.

I would like to create a query that will pick up the projects that the latest status should be “approved”. Based on this example, Project # 121 and # 144 should be picked up. Even though each project has the “approved” status, the latest record shows other status.

My goal is to have an entire record shows up. I have difficulties writing the query.

The problems that I have are:

1. When I used an aggregate function, I grouped by Project, Max on Status_date, Max_on Activity Date and Max on Status, the wrong status showed up, (with the criteria of status = “Approved”) If I substitute with Last, instead of Max, the wrong status shows up again.

2. I would like an entire record to be picked up in the query. For example, in Project # 144, the result should look like this:

144, 2/19/2006, 2/22/2006, Approved, Still waiting for the paperwork, Mary

If you have any suggestions, I will be very much appreciated. Thanks.

 

[PHV]

Something like this (SQL code) ?
SELECT A.Project, A.Status_Date, A.Activity_Date, A.Status, A.ChangedInfo, A.Changed_by
FROM Project_Info AS A INNER JOIN (
SELECT Project, Max(Status_Date) AS LastDate FROM Project_Info GROUP BY Project
) AS L ON A.Project = L.Project AND A.Status_Date = L.LastDate
WHERE A.Status = 'Approved'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Good one PHV. I came up with

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\Development\Projects\;" & _
          "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

rs.CursorLocation = adUseClient

rs.Open "Select Project, Status_Date, Activity_Date, Status, ChangedInfo, Changed_by " & _
        "From Projects.txt P " & _
        "Where Status_Date = (Select MAX(Status_Date) From Projects.txt X " & _
        "                     Where X.Project = P.Project) " & _
        "  AND Activity_Date = (Select MAX(Activity_Date) From Projects.txt X " & _
        "                       Where X.Project = P.Project AND " & _
        "                             X.Status_Date = P.Status_Date) " & _
        "   AND P.Status = 'Approved' " & _
        "Order By Project, Status_Date", _
        conn, adOpenStatic, adLockReadOnly, adCmdText

 

[Tamrak]

All,

Thank you so much. I modified your SQL codes and they were working perfectly. I do appreciate your time and prompt response.

You guys are the assets on this board. You guys are brilliant!