Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

Access Query - I need to reverse it. Ugh.

  • Please log in to reply




  • Member
  • PipPipPip
  • 626 posts
Well, here's the fictitious setup...

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?


  • 0


Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP