Not equal (1 Viewer)

[BryceR]

Currently I have a large database with about 30,000 zip codes in the U.S., I use these zip tables to designate which rep gets which lead (we have them broken up into regions in the U.S.). I run these leads through a query that is tied to the zip then to the region table that I made. So if I'm missing a zip it will drop that lead because it can't assign a region or it can't find a match for the zip code. My question is how do I build a query that will give me the leads that don't match then I can research the zip and find out where it belongs and add it to the zip table..

I hope this makes sence, Please let me know

Thanks!

 

[wh00t]

I presume that the region field would be empty for the zip code.

if so then try this

create a qry, and simply put Is Null in the criteria for the region field

 

[BryceR]

I've tried that several times in several ways, but it won't bring up any info at all.

 

[wh00t]

are region and zip stored in the same table?
if not how are they linked?

 

[BryceR]

no they are linked like this...

zip and Location # are on the same table
then location # and rep are on the same table
then rep and region are in another table

They are tied togeather by their like info so location from the first table is tied to location on the second then rep on the second is tied to the rep on the third....

I've tried to use IS Null for the Location # since that would be the first one not assigned, but it just comes up with no info I'm thinking that is because it shows that they all have a location #, but it's not looking at the origional table... I hope you can understand my rambleing...

 

[wh00t]

try this

table 1 zip and loc # - leave as is
table 2 loc # and rep - leave as is
table 3 rep and region, change to loc # and region

set the joins as follows

table 3 loc# to table 1 loc# 1 to many
table 3 loc# to table 2 loc# 1 to many

region is the central point here i.e. rep looks after a region, zip is in a region.

then try the query on table 1 where loc # Is Null