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.