Two lists

  • Thread starter Thread starter Galsaba
  • Start date Start date
G

Galsaba

I have two tables.
tblZip is a list of all ZIP codes. it has 3 fields: State, City, and ZIPcode
tblSchools is a list of schools. it has two fields: SchoolName and ZIPcode
I want to create a query that wil have 4 fields:
State, City, ZIPCode, CountSchool.
CountSchool will show how many schools each ZIP has.
If there is no school in a particular ZIP the CountSchool will have the value
0.

Thanks

Joe
 
Create a query that includes both tables. Join the tables on zipcode. Double
Click on the join line to change the type of join. Change it to the one that
says "include all records from your State, City, ZipCode table and ......". Pull
down State, City and ZipCode from the State, City, ZipCode table and SchoolName
from the other table. Click on the Sigma Button on the tool bar (looks like a
capital E) and change the query to a totals query. Under SchoolName, use the
drop down list and change Group By to Count. Run the query and you will get what
you want.
 
Or to get the results that they asked for: group by [Zip] and count
[SchoolName]
-ed
 
Back
Top