MIN and MAX for unique fields

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

Guest

[CityName] contains the names of the cities. [ParadeDates] contain the dates
of all the events for that city.

I need the query to return the MIN of the [ParadeDates] and also the MAX of
the [ParadeDates] for each city listed. The MIN and MAX would be in two
separate fields.

So if Houston is listed 5 times with date ranges from 1/1/2007,
2/1/2007, 3/1/2007, 4/1/2007 and 5/1/2007. The MIN would be 1/1/2007 and
the MAX would be 5/1/2007.

There will be multiple cities, so then if there is another city Los Angeles,
then the earliest and latest date should be shown for that also.

Thanks in advance.
 
[CityName] contains the names of the cities. [ParadeDates] contain the dates
of all the events for that city.

I need the query to return the MIN of the [ParadeDates] and also the MAX of
the [ParadeDates] for each city listed. The MIN and MAX would be in two
separate fields.

So if Houston is listed 5 times with date ranges from 1/1/2007,
2/1/2007, 3/1/2007, 4/1/2007 and 5/1/2007. The MIN would be 1/1/2007 and
the MAX would be 5/1/2007.

There will be multiple cities, so then if there is another city Los Angeles,
then the earliest and latest date should be shown for that also.

Thanks in advance.
Click to expand...

SELECT [CityName], Min([Parade Dates]) AS EarliestEvent, Max([Parade Dates])
AS LatestEvent
FROM yourtable
GROUP BY [City Name];

In the query grid, select the city name, and then select Parade Dates *twice*.
Make it a Totals query by clicking the Greek Sigma icon (looks like a sideways
M) and select Min for one of the date fields, Max for the other.

John W. Vinson [MVP]
 
Back
Top