Ms-access – Displaying only the most recent activity of an item

ms access

I have made a database that tracks the shipping and receiving of the tools that my company has. I am now wanting to make a query that shows only the most recent activity of all the tools. I have a current query that works pretty well but does not do exactly what I would like.

This is currently what the query is showing:
Current Query

This shows roughly what I want. The only problem is that as you can see bellow it allows Serial_Number to be listed twice. Once for when it was sent and once when it was received. I only want it to display the most recent of the sent and receive.

For example instead of having the serial number 050675 listed once on 2/25/2015 as sent and once on 3/3/2015 as received I would only have the activity on 3/3/2015. And I cannot just limit it to looking for when the item is received because if you look at Serial_Number 1 the most recent item is Sent.

So far I have tried nesting IIf statements and also tried the DMax function and i was unsuccessful on both of them.

This is what I am thinking it should look like. I know this is very very rough but I really do not know how to or what I should use in coding this.

If Serial_Number <> Unique Then Select Serial_Number Where Recent Date is greater

This is the table that the data is being pulled from:
enter image description here

Thank you for any help you might provide.

Best Answer

Try this:

    SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM <tablename> a
    INNER JOIN 
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM <tablename>
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]

Update:

In your specific case:

SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM tblSend_Recieve a
    INNER JOIN 
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM tblSend_Recieve
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]
Related Question