Using the highest count for the field name in a query

[Diggsy]

I have a table that list a count of peoples visits along with the State City
and 5 digit Zip Code. I would like to query this data so I can get a
total(Sum) of peoples visits by their 5 digit zip code. This is difficult
because some Zip Codes can have more than 1 town associated with that 5 digit
zip code. I would like the total for all trips in that zip code but have only
the city or town with the most visits listed in the city field. For instance
I would like the table below


City State Trips 5 Digit Zip
Anytown OH 1 18555
Anytown OH 2 18555
Farmtown OH 1 18666
Popularville OH 35 18666
Lakeville OH 1 18666
Hawley OH 7 18666

to roll up to

City State Trips 5 Digit Zip
Anytown OH 3 18555
Popularville OH 44 18666
So that you have the total for the zip code and the town with the highest
visits listed as the town for all zip codes
Thank you

Chris

 

[John Spencer]

QueryOne
SELECT ZipCode
, Max(Trips) as MaxTrips
, Sum(Trips) as TotalTrips
FROM TheTable
Group By ZipCode

Query Two based on the table and query one

SELECT T.City, T.State, Q1.SumTrips
FROM TheTable as T Inner Join QueryOne as Q1
ON T.ZipCode = Q1.ZipCode
AND T.Trips = Q1.MaxTrips

If you can't use that as the model for an SQL statement post back and I
will try to post instructions for using the query design view for
building the two queries. By the way it is possible that this could be
done in one query if your field and table names consist of only Letters,
Numbers, and underscores - no spaces, no punctuation characters, etc.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I have a table that list a count of peoples visits along with the State City
and 5 digit Zip Code. I would like to query this data so I can get a
total(Sum) of peoples visits by their 5 digit zip code. This is difficult
because some Zip Codes can have more than 1 town associated with that 5 digit
zip code. I would like the total for all trips in that zip code but have only
the city or town with the most visits listed in the city field. For instance
I would like the table below


City State Trips 5 Digit Zip
Anytown OH 1 18555
Anytown OH 2 18555
Farmtown OH 1 18666
Popularville OH 35 18666
Lakeville OH 1 18666
Hawley OH 7 18666

to roll up to

City State Trips 5 Digit Zip
Anytown OH 3 18555
Popularville OH 44 18666
So that you have the total for the zip code and the town with the highest
visits listed as the town for all zip codes

SELECT (SELECT TOP 1 Min(X.City)
FROM thetable As X
WHERE X.Zip = T.Zip
GROUP BY X.Trips
ORDER BY X.Trips DESC
) As TheCity,
State, TrIps, Zip
FROM thetable As T
ORDER BY 1