Zip Code/Market Area

[Jason Spence]

I have a table of several hundred customers and I want to
run a query/report that shows me in what DMA Market these
people are in. I have a list that I can import as a table
that lists all the DMA's by zip. So basically I want to
run a query/report that will tell me something like this:

Birmingham: 20
Atlanta: 30
Houston: 10

PLEASE HELP ME!

 

[John Vinson]

I have a table of several hundred customers and I want to
run a query/report that shows me in what DMA Market these
people are in. I have a list that I can import as a table
that lists all the DMA's by zip. So basically I want to
run a query/report that will tell me something like this:

Birmingham: 20
Atlanta: 30
Houston: 10

PLEASE HELP ME!

Create a Query joining your table to the DMA Market table by Zipcode.
Select the Market Area field *twice*. Change it to a Totals query by
clicking the Greek Sigma icon (looks like a sideways M); Group By one
instance of the market area and Count the other one.

 

[Jason Spence]

That did not seem to work. The way DMA's work is it
breaks apart the zip into the first 3 #'s and the last 2
#'s. The DMA is the first 3. So like where I live in
30004 the DMAis 300 which is Greater Atlanta.

So I have a customer table that has name address, city
state zip (the 5 digit one)

And I have another table that lists DMA name and the 3
digit part of the zip.

Any other suggestions?

 

[John Vinson]

That did not seem to work. The way DMA's work is it
breaks apart the zip into the first 3 #'s and the last 2
#'s. The DMA is the first 3. So like where I live in
30004 the DMAis 300 which is Greater Atlanta.

Sorry. I wasn't aware of that (I haven't done any work with DMA's),
and you didn't say.

So I have a customer table that has name address, city
state zip (the 5 digit one)

And I have another table that lists DMA name and the 3
digit part of the zip.

Use the expression

Left([Zip], 3)

to extract the three digits, and join on that calculated field.