Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 22nd, 2003, 02:59 PM
Registered User
 
Join Date: Dec 2003
Location: Miami, Florida, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access Problems. Creating a Transition Table

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.
Reply With Quote
  #2 (permalink)  
Old December 22nd, 2003, 04:02 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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.

Reply With Quote
  #3 (permalink)  
Old December 22nd, 2003, 04:50 PM
Registered User
 
Join Date: Dec 2003
Location: Miami, Florida, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old December 22nd, 2003, 10:53 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old December 22nd, 2003, 11:28 PM
Registered User
 
Join Date: Dec 2003
Location: Miami, Florida, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #6 (permalink)  
Old December 23rd, 2003, 06:03 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old December 23rd, 2003, 06:44 PM
Registered User
 
Join Date: Dec 2003
Location: Miami, Florida, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Server Transition nowak ASP.NET 2.0 Basics 3 September 26th, 2007 03:04 PM
Transition to .NET..... how difficult? BananaJim ASP.NET 2.0 Basics 3 November 2nd, 2006 12:44 PM
Creating new Table Row in Access with ASPX BramuS ASP.NET 1.0 and 1.1 Basics 3 May 9th, 2005 09:22 AM
Creating table with existing table without value kumar_kumar Oracle 1 January 4th, 2005 07:12 AM
Access 97 Table locking problems timmaher Access VBA 1 November 5th, 2003 11:05 AM



All times are GMT -4. The time now is 06:26 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.