Two lists

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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"Galsaba" wrote in message
...
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

Or to get the results that they asked for: group by [Zip] and count
[SchoolName]
-ed

"PC Datatsheet" wrote in message
link.net...
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"Galsaba" wrote in message
...
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