Hopefully somebody has a neat answer for this one, cause it's driving me a little bonkers.

Ok, I got an Access Database with a table called ZipCityState. The table has been populated with info that I got from a basic list. About 70,000 records. Looks like this:

uid | Zip | City | State

The table is correct, each record correlates correctly. zip = city = state.

I'm going to be using a many to many scenario because I want users to choose the state from a drop down list, Then the city based on that choice, then the zip based on that one, so I created other tables with info from ZipCityState:

A table called States

uid | States
it's been populated

Then a table called Cities

uid | Cities
it's been populated

Then table Zips
it's been populated

Now I'm back at the main ZipCityState table. I've made a copy of it and called it ZCStransitional. (You need a transitional table for many to many scenario's, right?)

What I want know is this -

How do I tell Access to find and replace like this -

I want to replace the contents of the States column with the uid of the States Table
I want to replace the contents of the Cities column with the uid of the Cities Table
...and, I want to replace the contents of the Zip column with the uid of the Zip Table

I know, I know. Jeez!
But I was hoping someone might have an answer. I've tired exporting this to Excel, but I can't figure out a formula for it. I've tried creating a query, but, alas, I just don't know how.

Any help....PLEASE!?


Ambivalent? Well, yes and no.

Within access create and update query
using the first table as your source and the second as your destination. Line the fields from the first table that you want to replace with the second table.

Not quite following you on this one. I'm sorry for my complete noob like ignorance.

Ok, if I create an update query in Access
1. I go table by table. In otherwords, I choose the Cities table first, and update to XipCityState. Then create a new update query for States, updating to ZipCityStates, then last Xips, and update to ZipCityStates. Am I right?

2. I'm not following the instructions to well, I think, in the create Query dialog. Am I supposed to create the relationsship by dragging the relationship arrows. How do I create the argument? The argument is:
Where tblCities.Cities = tbl.ZipCityState.Cities, then replace with tbl.Cities.uid

Or am I up S--t's creek cause I'm a dumb noob.

I guess I need a step by step on this one. If somebody can show me how to do one, I can figure out the rest.
Thanks

Ambivalent? Well, yes and no.

States have vities and cities have zip codes, but two cities can have one zip code and two zip codes can have one city, some times one zip code can be in two states. (it happens). Really, each of this records does not have anything to do with each other.

Why are you going around it this way? What do you want as a result of this process?





Sal

Sal,

Well, I'm trying to create a solution for a moving company. First I wanted to map out the database in Access to make sure I was going the right way. I was doing fine until I got to the ZipCityState choices.

My list is pretty straight forward, and contains only "major" cities. (How'd they figured out that one is beyond me) That's why only 70,000 records. If it was complete, it would be about 120,000 records. Yes, I agree, zips often are in two cities, and there are many Cities that are not unique, they reside in different states. That's one reason for the many to may scenario. I check with the good ol' USPS and they categorize zips as belonging to one city. When an address reads a zip for that city, but an actual different city, they still send it correctly because the local office is designed to handle zips first, then cities. So I'm going with their explanation.

Something I forgot to mention before

tblCities looks like this > uid | Cities | StateID
tblZip looks like this > uid | Zips | CityID

Ok, so why am I creating a main transistional table? So that I can make three drop down lists on a web page (I have another reason I'll explain in a second)

I want the first dropdown to be populated by tblStates

Then the next drop down populated by cities where tblCities.StateID = tblStates.uid

Then the same argument for the next drop down list from zips.

I'm creating an option for records not found in the tables.

Now, I need the main ZipCityState table to be a transisitional table for the following reasons.

The back end administration has to consider addresses as beloning the Regions

So I'm creating two more tables

tblRegions will look like > uid | Regions

and then a transistional table between Regions and States

tblRegionState will look like this > uid | RegionID | StateID

Now, am I spazzing out?

If I'm all wrong, how would you go about it?

Ambivalent? Well, yes and no.

a transitional table lins two tables toghether. one Order can have two or more itemsor none. one item can have two or more orders or none. A transition table allows you to do this. For what you are saying, one zip can go to only one city and one city to only one state. I do not see the need for a many to many relationship.

The first drop down gives you the states, the second one gives you the vities and the third the zip. The first deopdown is bound to the state table. The second is bound to the city table, which will contain the state as a foreign key field. The third will be for zip, which will contain the city as a foreign key.

You use this foreign keys to query the tables and show only the corresponding values for the city and zip values. same is true for region. It just depends on what a region is composed of (states, cities or zip codes).

The combination of state and city make a primary key for your city table. I do not see the need to slow down your databasewith so many many to many relationships. Yes, there is a clarksville TN and a clarksville, Arkansas, and a clarksville, Arizona,but I would not create only one entry in the city table for clarksville because I know that they are two different cities anyway. Each with a different population, location longitude and lattitude, etc.

Don't use many to many.






Sal

Sal, thanks for giving me your take. But my problem persists.

tblCities has uid | Cities | StateID

the StateID field is NOT Populated. Only the uid and the Cities field. Being that I populated the the table from my first ZipCityState table. The zips table MUST be filtered by StateID, because zips can belong to more than one city, but are basically bound by state, (in my app, not the real world.)

What it boils down to is this.

How do I get the uid of tblStates into the StateID column in tblCities and tblZips, based on their relation in the main ZipCityState table, using Access?

In other words,
in the ZipCityState table, under the different columns, it looks like this
zip city state
05505 | georgetown | confusion
05575 | georgetown | confusion
05762 | georgetown | confusion

This table tells me that georgetown is in confusion

tblState tells me that confusion has a uid of 32

how to I get the number 32 into the StateID column in tblCities programmatically in Access?

Sorry I have gone around and around on this one, but I even confused myself on this puppy.

The many to many relation comes up when you have Miami, Florid and Miami, Georgia
See how that works?

Ambivalent? Well, yes and no.