totalling school enrollment by city with duplicate cities

[Guest]

I ran a query with city, state and school enrollment and I want to know the
total enrollment from all schools in each city. There are duplicates of many
cities.

Thank you in advance for any help.

 

[Al Camp]

Troy,
You wanted...

total enrollment from all schools in each city.

You'll have to include the State in your query in order to differentiate
which City you're reporting on. Using a parameter query you'd need to
prompt for both. City alone will not do it.
hth
Al Camp

 

[Guest]

Al,
Thanks, I did query for state, city and enrollment. The problem is that I
get duplicates of the same city because there several schools in each city
and I want to know the total enrollment of all the schools in each city.

 

[PC Datasheet]

You need tables that look like:
TblState
StateID
State

TblCity
CityID
StateID
City

TblEnrollment
EnrollmentID
SchoolName
CityID
Enrollment

You then need to do a totals query based on TblEnrollment grouped on CityID.
Even if you have two cities with the same name in the state, they will have
different CityIDs. The totals query will then give you an enrollment for
each separate city.

 

[Al Camp]

Well, I still think you may have a problem with your query.
Are you doing a totals query?
I assumed that there would not be any duplicate cities within a state.
I've never heard of that... but perhaps it could happen.
Given a table with...
State
City
SchoolName
Enrollment

and a totals query of... (there might be 4 schools in Manchester)
State="NH"(Grouped) City="Manchester"(grouped) Enrollment(Sum)
should give you the total you need.

your post...

The problem is that I get duplicates of the same city<

leads me to believe that your totals query has not successfully filtered
out all the other states that might have a Manchester, thereby throwing off
the totals.

Al Camp

 

[Larry Linson]

If I understood, there may be multiple records for each city, not multiple
cities with the same name in the same state.

A totals Query summing Enrollment, grouping on City and State, is the way to
start... then you can work with that to create your Forms / Reports / etc.

Larry Linson
Microsoft Access MVP

 

[Al Camp]

Larry,
If your reply was to me, I guess I'm still "confoosed"... wasn't that my
solution?Thanks,
Al Camp