[RESOLVED] Access SQL Query help

[jasonwucinski]

Hello everyone. I'm hoping someone can help me with this query. I have 4 table:
ADMIN_USERS, CLIENT_DIR, CLIENT_INFO,CLIENT_ACTIVITY.
ADMIN_USERS has a one-to-many with CLIENT_DIR and CLIENT_DIR has a one to many to both CLIENT_INFO and CLIENT_ACTIVITY.

I want to pull a few columns from each table. I dont think im having a problem with the join. The problem is this: In CLIENT_ACTIVITY i have four columns: TransactionID, ClienID, ClientActive and ClientStatusChangeDate. This table may have 1 or more records for each ClientID. For each ClientID i want to select the one with the most current ClientStatusChangeDate (which is a date value) .

so, if i have 12 records, six that have ClientID= 1 and six that have ClientID = 2, I want to select one record from each group ( ClientID= 1 and ClientID= 2) where its respective ClientStatusChangeDate is most recent. so, i should end up with two records. one where ClientID that = 1 is most recent and one where ClientID that = 2 is most recent.

here is what i have so far:

            str = "SELECT CLIENT_DIR.ClientID, CLIENT_INFO.FirstName, CLIENT_INFO.LastName, CLIENT_ACTIVITY.ActiveStatus, CLIENT_DIR.AdminID, ADMIN_USERS.LastName, ADMIN_USERS.FirstName,  (Select MAX(CLIENT_ACTIVITY.StatusChangeDate) FROM CLIENT_ACTIVITY) AS T, CLIENT_ACTIVITY.ActivityID" _
& " FROM ADMIN_USERS INNER JOIN (CLIENT_DIR INNER JOIN (CLIENT_INFO INNER JOIN CLIENT_ACTIVITY ON CLIENT_INFO.ClientID = CLIENT_ACTIVITY.ClientID) ON (CLIENT_DIR.ClientID = CLIENT_INFO.ClientID) AND (CLIENT_DIR.ClientID = CLIENT_ACTIVITY.ClientID)) ON ADMIN_USERS.AdminID = CLIENT_DIR.AdminID" _
& ";"

This code will return all records for each clientID.

thanks
jason

[jasonwucinski]

never mind, i figured it out:

            str = "SELECT CLIENT_DIR.ClientID, CLIENT_INFO.FirstName, CLIENT_INFO.LastName, CLIENT_ACTIVITY.ActiveStatus, CLIENT_DIR.AdminID, ADMIN_USERS.LastName, ADMIN_USERS.FirstName," _
            & " CLIENT_ACTIVITY.StatusChangeDate, CLIENT_ACTIVITY.ActivityID" _
& " FROM (ADMIN_USERS INNER JOIN CLIENT_DIR ON ADMIN_USERS.AdminID = CLIENT_DIR.AdminID) INNER JOIN (CLIENT_INFO INNER JOIN CLIENT_ACTIVITY ON CLIENT_INFO.ClientID = CLIENT_ACTIVITY.ClientID) ON (CLIENT_DIR.ClientID = CLIENT_INFO.ClientID) AND (CLIENT_DIR.ClientID =CLIENT_ACTIVITY.ClientID) where CLIENT_ACTIVITY.StatusChangeDate = " _
& " (Select MAX(CLIENT_ACTIVITY.StatusChangeDate)  FROM CLIENT_ACTIVITY  where CLIENT_ACTIVITY.ClientID =  CLIENT_DIR.ClientID ) AND CLIENT_ACTIVITY.ActiveStatus = " & getFilter & "; "