Mailing Database question

[news.epix.net]

I have 2 tables in an Access 2000 database

One table is a mailing Database which contains Names, Address, and Zip
Codes in 3 digit format (the first 3 digits of a 5 digit code)
It contains over 290,000 records in the following format:

example: Mary Dobsin 123 Main St. York PA 174
example: John Smith 56 John's Ln. NYC NY
182

The other table is a Salesperson Territory database.
It contains the name of the 27 salespeople and what zipcodes they cover
(also in a 3 digit format) in the following format


example: Territory 1 Jeff Goodwin 174
. .
. .
. .
example: Territory 8 Jeff Goodwin 182




What I need to do is count how many records (or customers) in the Mailing
Database each Salesperson has in their territory


Any help would be GREATLY appreciated

 

[John Spencer (MVP)]

SELECT SalePerson, Count(Customers.ZIP) as CustomerCount
FROM SalesTerritory INNER JOIN Customers
ON SalesTerritory.ZIP = Customers.ZIP

Post back if you don't know how to do this as SQL and we can describe the
process using the query grid. Hint: Look for the Totals choice in the View menu.

 

[news.epix.net]

As I am a newbie with Access/SQL I can't follow your code. I do appreciate
the help though.

Here would be the questions I have if I'm doing it in the Query window

1.) What type of relationship needs to be established between the 2 tables?

I am assuming a one to many relationship between the Sales Team table (holds
the list of the Sales People aka Territory Managers and all the zips they
cover) to the RIdeas mailing database (holds all the customers names &
addresses) as each Territory Manager covers many zips and will have many
records in the RIdeas mailing database that will correspond to them.

2.) What do I do with the Zip3 field ( see below) ?

I am adding 3 fields to the query Window

Territory Manager
Zip3 (the name of the field holding the 3 digit zip codes in the Sales Team
table)
Zip Code (the name of the field holding the 3 digit zip codes in the RIdeas
mailing list table)

I am Grouping By Territory Manager
I am counting Zip Code
I don't know what to put in the Zip3 field

Logically I want it to count 1 for each time the Zip Code in a record in the
RIdeas mailing list table matches the zip3 of the Sales Team Table.

I tried using comparisons (Zip Code = Zip3) in the Zip3 field and they don't
seem to work.

 

[John Spencer (MVP)]

WHOOPS! My apologies. I left off a line in that SQL statement. Here is the
revised SQL statement based on your field and table names.

SELECT [Territory Manager], Count(RIdeas.[Zip Code]) as CustomerCount
FROM [Sales Team] INNER JOIN RIdeas
ON [Sales Team].Zip3 = RIdeas.[Zip Code]
GROUP BY [Territory Manager]

You can select View: SQL when you have the query open and then type in the above
and try to run it.

Open a new query window.
Add your SalesTerritory and your Customer tables to the grid
Join SalesTerritory Zip Code to Customers Zip Code by dragging from one to the other
Add the SalesPersonID and the customer zip to the field
Select Total from the View menu
That will add a new line to the grid Labelled Total and will set each each to
group by
Under the Customer Zip Code change the Group By to Count

Hopefully that will give you what you want.

If it still fails, then I would suspect that one of the tables has extra
characters (trailing spaces?) in the ZIP code field.