crosstab query - find maximum Value each Day, report time of day

  • Thread starter Thread starter David
  • Start date Start date
D

David

My table contains 3 fields: LOCATION, DATE, TIME, VALUE

I need to build a crosstab query that Groups By LOCATION & DATE, finds
the minimum VALUE each day, and then reports the TIME of day that the
minimum value was found.

Seems easy to get a crosstab or Totals query to get the minimum value,
but to then get the query to report a different field is escaping me.
Any recommendations?

(Sorry if this is a repost, something bugged during the first send
attempt)

thanks much!
David
 
ummm that's 4 fields of which 3 happen to be either a function or property.
If it isn't too late, consider different field names for Date, Time, and
Value.

I would first create a query that finds the minimum VALUE for each location
and date. Then join this query to the full table to filter out all records
that are not the minimum value for each location and date. Change this query
into a crosstab. You didn't say whether the LOCATION was a column heading or
row heading.
 
Thanks Duane. You're right, 4 and not 3 (actually, there are others
fields too, but not important to the problem at hand). Location is a
row heading (so want a the max VALUE for each DATE and LOCATION. So
there are 22 LOCATIONS and there should be 22 ROWS for each DATE
reporting the VALUE.

I had thought about your solution, but was thinking there might be a
better solution using nested aggregates or something? Also, I was
thinking it would be a problem if there were multiple records returned
(multiple records with same min or max value), but I guess this can be
dealt with by averaging the times in the crosstab.

David
 
I would caution against using nested subqueries in crosstabs. From my
experience, Access/Jet gets confused with some more complex sql in
crosstabs.
 
Back
Top