I sell canned beverages at local events from a booth. When I return, I get my inventory into an access dbase program I designed.
I created, or am trying to create rather, a report that will show me what I sold at the last event. In my mind, this is how it would work - A query would look at sales and pull all data from the most recent date only (which would be the last booth I worked no matter if it was yesterday, last week, last month, etc.) and display how many of each product I sold on that day.
The problem I'm having is that I set the query up as "Select" query with "Totals" and set the date field to "Max". In the same query, my items are listed as "Group By". So now, the query is pulling the last date I sold each item, not how many of each item I sold on the last date. It's totally backwards from what I'm trying to achieve.
Let's run through that current method I have set up so you can see what I mean. If, on the first of March I sold Cokes and Sprites, and then, at another booth on the 15th I only sold Cokes. When I run this report, I'd like it to pull up ONLY the sales from the 15th. In this example, I want it to only show me the cokes sold on the 15th as no Sprites were sold on that day. Instead, the query is showing me the latest date each item was sold so I'm seeing Sprites for the 1st and Cokes for the 15th.
I understand why it's doing this, but I'm not coming up with a way to show only items sold on the most recent date.
Any ideas?
Thanks,
Magus