Help with the Max feature

[Shannonvip]

I have a table that contains shipping records for the month of January. This table contains the serial number of the item sold which is unique, the customer name and the date it was sold. If a customer returned an order or one unit off of that order we put those items back in stock and resell them out. If they get sold out in the same month, January for example, it will show up on the report as many times as it was sold out. So, I can have one serial number show up on this report, that I upload as a table, multiple times with different customer and date information. The only record I need to keep is the one with the latest date. I have been manually deleting the records, which takes forever. Someone mentioned to me that they thought there was a max function that could help me but they didn't know how to do it.

Can someone be so kind as to walk me through this?

Thank you in advance.


Shannon

[JoeM]

I think you can use an Aggregate Query to get what you want, like this:

1. Create a new query
2. Add the Serial Number and Date fields to it
3. Click on the Totals Button (looks like a Sigma). This will add a Totals Row with the phrase "Group By" under each of the two fields.
4. Under the Date field, change the "Group By" option to "Max"

Now, if you view this query, it will show you the Max date for each Serial Number.
To get the value associated with that date, do the following:
1. Create a new query, joining the query you just created with your original data table on both the Serial Number and Date fields
2. Return the Serial Number, Date, and Amount fields from your original table

If I have understood your correctly, I think that should give you what you are looking for.

[Shannonvip]

Thanks Joe! What if there is other data that I also need but didn't mention because it wasn't important. Other fields like sales rep etc.? I need the entire table to show up with the results. I am understanding and created the query with "group by" and "max" but I am getting lost and part two. I know how to run a query off of a query and a table. Thanks so much

[JoeM]

In the second query, you can actually add as many fields as you like from your initial table (you could even add all of them, if you like!).