Problem returning two linked values from a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to design a query that will tell me the maximum value for a
particular category, and the earliest date that that maximum value occurred
on. For instance, the maximum number of red boxes sold, and the first date
that that maximum number was reached.

I have the query set up to return category, value and date, with a "group
by" sum function applied. When I set the "group by" to "max" for value and
run the query it returns what looks like every date entered for the
category... or something. When I also group by "min" or "first" in the date,
it will return just one entry for each category, corresponding to the max
value.

The problem is, the date it returns matches up with the first occurrence of
that max value, regardless of category! I want the date for *that* category.
I've spent a rather frustrated few hours trying to figure this out, but can't
seem to find a way to make it link the date to the max value it corresponds
to. I would've thought that if you did a query to find a particular value, it
would return all corresponding values from that entry as if they were locked
together. But it doesn't seem to be treating it this way. I get the maximum
number of red boxes sold, say 60, and then the first date that 60 sales of
*whatever* occurred on, not necessarily red boxes.

I'll admit that I don't have extensive experience with Access, so I could
have just missed something quite obvious. Any suggestions?
 
Hi

This can be written in a single query but if you want to keep it "simple"
(purely point and click) you should use 2 queries...

1: Set up a query to get the maximum value per category

Using the show table dialog add the table tbl_sales (or whatever you have
called it)

Column1: Category - Group By
Column2: Value - Max

Save this as something like qry_max_value

2: Set up a 2nd query

Using the show table dialog add the table tbl_sales
and add the query qry_max_value

Make 2 joins between these...

category to category
maxofvalue to value

Then set the columns up as follows...

Column1: Category - Group By
Column2: Value - Group By
Column3: Date - Min

Hope this Helps

Regards

Andy Hull
 
Eureka! Thank you so much! It worked just fine. I'd actually tried doing it
as two queires before, but something I was doing was not quite right, I
guess, 'cause I'd get an error on the second join. Whatever it was, works now.

Thanks also for making it easy to understand - I was a bit worried about a
lot of technical language.
 
Back
Top