MIN and MAX for unique fields

[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.

 

[Jeff Boyce]

If I understand your data design, you could use a Totals query, grouping by
CityName and selecting Minimum (or Maximum) for ParadeDates.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John W. Vinson]

[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.

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]