Match Up Data From 2 Tables

[Escondido]

I've got a database of about 6 million contacts with the standard fields

First Name
Last Name
Address
Address2
City
State
Zip Code
Phone1
Phone2
DOB
Income
Etc.

However, hardly none of them have the County listed and I'm looking to purchase an Access Database containing every county in the USA. It'll look something like the following.

Zip
City, State
County
Area Code

I'm not entirely sure if I can somehow run a query using the 2 databases together to get the results I want or if I should see if it's possible to get code to compare the zip codes of each row to the county database and update the whole thing. The file is almost at the 2 GB mark, so I don't think that's feasible unless I figure out how to link it to Sql Server 2014.

My goal would be to pull up all the records for a specific county, but that might present some problems as it seems the Access Or command has a limit of 9 or so terms.

Does anybody have a recommendation about some options I might pursue?

 

[Ranman256]

Put each table in its own db.
The tContacts table would be the only table in its db.
Same with tZipCodes.
These 2 DBs would link their table into the main db. (To save space)

You can back fill the tZipCode counties from the client table.
Others can be filled from the USPS zip code file.

 

[plog]

... I'm looking to purchase an Access Database containing every county in the USA.

I've been with organizations that were crazy about buying data. You do not need to purchase a dataset to get County/Zip info in the USA. I'd try the USPS site, or just google it. That data doesn't need to be purchased.

 

[Escondido]

I've been with organizations that were crazy about buying data. You do not need to purchase a dataset to get County/Zip info in the USA. I'd try the USPS site, or just google it. That data doesn't need to be purchased.

I scoured the Internet trying to find free databases, but most of them had some type of limitation or weren't updated in a few years. I'd love to find a reputable source, but for the time being I'm ok with paying bit of money. I found 2 sources for less than $30, so that's not too bad.

 

[Escondido]

Put each table in its own db.
The tContacts table would be the only table in its db.
Same with tZipCodes.
These 2 DBs would link their table into the main db. (To save space)

You can back fill the tZipCode counties from the client table.
Others can be filled from the USPS zip code file.

I appreciate your help, but I want to make sure I understand this correctly.

mContacts is my old database and since it's the only table in it I can still use that. Will it matter if the current database has about 30 different queries in based off the master database or is it better to simply copy the old table to a new database?

Zipcodes will be its own database

fContacts (final Database) will be used to store all of the data between both databases? If that is the case, should I focus on learning how to use an update query or lookup table?

And what do you mean by backfill the counties table? I imagine the client table is fContacts, but I might be misunderstanding you.

Thanks.

 

[Ranman256]

USPS is free.

 

[Escondido]

USPS is free.

One would think that considering the entity, but I'd love to know where you were able to get the most updated list for free. AIS pdf shows they charge depending on the database


ribbs.usps.gov/addressing/documents/tech_guides/orderforms/AIS002.PDF

 

[Escondido]

I was able to solve the problem. Appreciate the help, though.