Re: Mailing Database question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 02/16/05 

Date: Wed, 16 Feb 2005 18:51:30 -0500

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.

"news.epix.net" wrote:
>
> 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)" <spencer4@comcast.net> wrote in message
> news:42113D28.8FB2DEEE@comcast.net...
> >
> > 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" wrote:
> >>
> >> 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

Relevant Pages

  • Re: [fw-wiz] How to Save The World
    ... _EXACTLY_ that kind of garbage. ... people who don't understand a sales cycle. ... stuff only "convinces" a customer who's already convinced (one way ... by hundreds of list members emailing me, "yeah, I buy stuff from ...
    (Firewall-Wizards)
  • Re: Can I use an old license on a new computer?
    ... I haven't done a lot of computer shopping in a couple years, but I don't recall it ever being suggested in either advertising or face to face interaction with a sales person that I purchase a Gateway because they provide a true oem installation cd with their computer. ... I would think that it would be to gateway's distinct marketing and sales advantage to tout such an important customer satisfaction consideration and to advertise such to anyone that would be within eyesight or earshot. ...
    (microsoft.public.windowsxp.general)
  • Customer Applications Scientist *HPLC*
    ... Western Region Customer Applications Scientist ... The Western Region Customer Applications Scientist will support the ... will demonstrate Eksigent's HPLC systems in the Eksigent laboratory ... Act as the technical expert for the sales team in presenting ...
    (sci.chem.analytical)
  • Re: Auction Terms
    ... I've worked in sales for many years. ... customer feel valued. ... Even today, as a Quality Engineer, I still use sales ... > You think as any reasonable seller Bob, ...
    (rec.collecting.coins)
  • Re: New and Refreshed Advertising Colateral for OpenVMS
    ... >> outright lies effort to try to justify it to its customer base. ... > VMS sales would have been better. ... heard that when positive advertising and marketing action occurs, ...
    (comp.os.vms)