1

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.

  • 1
    Recieve should be: Receive (not related to the question, just an observation). – ypercubeᵀᴹ Mar 10 '15 at 19:09
4

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]
  • Thank you for your answer. Maybe I am misunderstanding this, but the purpose of inner join is when you have data in two different tables that you want to display. All of my data is in a single table that the query would be pulling from. I will update the post with a screen shot of the table I am pulling from. Sorry for not including this info originally. – Caleb Duff Mar 10 '15 at 18:18
  • That's probably the most common usage of an INNER JOIN but there's nothing stopping you joining a table with itself (or a flavor of itself). – Queue Mann Mar 10 '15 at 18:23
  • Sure- what this does is take a set from the table which is the latest [recent date] for each serial_number and then returns the corresponding data for that value. Have you tried it yet? – Thomas Cleberg Mar 10 '15 at 18:24
  • I did try it. I obviously edited the code for my case and it gave me a message saying there was an error in the FROM clause. – Caleb Duff Mar 10 '15 at 18:34
  • 1
    MAX([Recent Date]) AS [Recent Date] will work. I forgot you have to use the explicit AS in Access. – Thomas Cleberg Mar 10 '15 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.