How do I filter a query - report to only show the records with the most recent date?

[joey]

I am having trouble getting a query or report to show only the most recent data.

We have salesmen that use a handheld data collector scanners to count inventory in stores. The scanner data is imported to a Access table. Each record line is one scanned item. I have a query with totals that counts the records and gives me a total count of each item at the store on that date.

I then need to filter the data to only show the most recent date. Using Max Date I get the most recent date but the count fields are showing totals for all dates. I am also getting the unique item from the earlier date in this query which I do not want.

Here is my data table: Inventory Scans from stores.

Scan Date Item Scanned location 1/1/2014 item123 Store ABC 1/1/2014 item123 Store ABC 1/1/2014 item 456 Store ABC 1/1/2014 item 456 Store ABC 1/1/2014 item 456 Store ABC 1/1/2014 item 789 Store ABC 2/1/2014 item123 Store ABC 2/1/2014 item123 Store ABC 2/1/2014 item 456 Store ABC

Here is my Query with Totals that counts the item records:

Scan Date Item Scanned location (Item Scanned) count 1/1/2014 item123 Store ABC 2 1/1/2014 item 456 Store ABC 3 1/1/2014 item 789 Store ABC 1 2/1/2014 item123 Store ABC 2 2/1/2014 item 456 Store ABC 1

This is what I am trying to get - only the most recent date of counted items:

Scan Date Item Scanned location (Item Scanned) count 2/1/2014 item123 Store ABC 2 2/1/2014 item 456 Store ABC 1

This is my first post so I hope I didn't break any rules or present my question in a bad way or take up too much room. Thanks for any help.

[June7]

An aggregate (Totals) GROUP BY query might get what you want. Access Help has info on using the query builder.

Try:

SELECT [Item Scanned], Location, Max([Scan Date]) AS MaxDate, Count(*) AS CountItemLoc FROM tablename GROUP BY [Item Scanned], Location;

[joey]

Hi,
Thanks for the quick response!
This returns the MAX date correctly in the date field but the item scanned (UPC) count is still totalling all records (all dates.)

[June7]

Filtering records based on value in other records of same table is tricky. If what you want to do is exclude all records that do not match the maximum date for each group, this could require subquery or domain aggregate functions.

Review: http://allenbrowne.com/subquery-01.html#TopN

[joey]

Yes, I want to exclude all records that do not mathch the maximum date. The AllenBrown referral has great stuff but a bit beyond me at this point.
Here is the SQL code that I have:

SELECT [Master Query - Inventory Scans].CUST_LOOKUP, Max([Master Query - Inventory Scans].Scan_Date) AS MaxOfScan_Date, [Master Query - Inventory Scans].Rep_Name, [Master Query - Inventory Scans].Name, [Master Query - Inventory Scans].UPC, Count([Master Query - Inventory Scans].UPC) AS CountOfUPC
FROM [Master Query - Inventory Scans]
GROUP BY [Master Query - Inventory Scans].CUST_LOOKUP, [Master Query - Inventory Scans].Rep_Name, [Master Query - Inventory Scans].Name, [Master Query - Inventory Scans].UPC, [Master Query - Inventory Scans].Display_Name;

[June7]

Problem with trying the TOP N approach is the query does not have unique ID field. I presume the table does. So do the TOP N query on the table then do an aggregate query on that query.

Otherwise try domain aggregate approach:

SELECT * FROM [Master Query - Inventory Scans] WHERE [Scan Date]=DMax("[Scan Date]", "[Master Query - Inventory Scans]", "[Item Scanned]='" & [Item Scanned] & " AND location='" & [location] & "'")