Multi Tables Selectable from a Second table.

stewartrose

Registered User.
Local time
Today, 09:30
Joined
Aug 11, 2003
Messages
16
Could someone Help please,

Imagine two feilds States and County, in the staes column a pull down menu with 51 states, but you cant have 1800 countys in the County column, because some County names are used in more than one state name, so there needs to be a way that when you select a state a new database of countys for that state be put into the County column as a pull down menu.

Any ideas please would be great..

Best reguards from Alan
 
Would you like to allow for a county name to be entered one but used multiple times in regard to a state? Or would you just want to enter every county by state. There are a few ways to do this, but it will break down to the two being in separate tables and the proper relationships between them.

This structure would require that even though the county name would be in the system, you would have to enter it for every occurrence for each state.

tblState
-StatePKey
-StateName

tblCounty
- CountyPKey
- StatePKey
- County Name

This structure allows for entering a county name ONCE and associating it with several states.

tblState
-StatePKey
-StateName

tblCounty
- CountyPKey
- County Name

tblStateCounty (junction tbl)
- StateCountyPKey
- StatePKey
- CountyPKey

The junction table would allow you to create a unique index of a county name to several state names and a State name to several different counties. The basis of this would be a many-to-many relationship, and when this is encountered I have always learned to insert a junction tbl to break it into two one-to-many relationships.
Since your post was in the tables catagory, your descriuption would sound just like the junction tbl itself, allowing for the combination of information from the two seperate tables.
 
Alan,

Table 1
=====
State Number
State Name

Table 2
=====
County Number
Count Name

Table 3
=====
State Number
County Number

This is the standard method for many-to-many relationships. Table 3 is the "junction" table. It represents that fact that a State contains many Counties and a County Name may be in many states.

HTH,
RichM
 
Rich,

Would the two fields in this example table be a concantinated key?

Table 3
=====
State Number
County Number

Otherwise, look like great minds think alike.
 
Multiple Tables

Sorry if I have entered this post in the wrong place, but thank you all for a fast reply, I will try and explain a little further.

I have 51 states in a pull down list, and one column called County
But I have 51 small databases one for each state filled with the countys for that state

Then if someone selects say Michigan on the states column, the database for that state would show up as a pull down menu on the County column, filled with Countys.

Sorry if I am not explaining correctly.

and thank to all for the help.

Best regards from Alan
 
In a relational DB system, there wouldnt be that many tbls. But if that is what you have to work with, we can work with it.
Are you wanting to create a TABLE or a FORM that would allow for this seleciton process? If you are using a FORM then you would base the second combo box off of a query that uses the first combo box as a parameter / data filter source.

Am i getting closer?
 
jeremie,

Yes, the 2 fields are a concatenated primary key, to avoid duplicates.

alan,

As jeremie wrote, you do not want 51 tables of counties. You would create one list box based on States. Next, you would create a query that uses the State selected. This query selects all County Numbers from the junction table where the State Number is the value selected from the State combo box. This query is the RecordSource for a Count list box.

Finally, you Requery the County list box to get a list of the Counties for the selected state.

Try a forum Search using the keywords "cascading combo box" or "boxes". You will find several threads with examples.

HTH,
RichM
 
Multiple Tables

Hi jeremie & Rich,

Thank you both for your help, a sample would be a great help, at my age the old grey cells are faiding fast so new learning takes a long time, I did a search for samples on the search word you told me to use, but I could not find any, could you direct me to one, that you think would do what I need, I have merged all the countys together in one file as suggested. so I am learning.

Thanks again to you noth

Best regards from Alan
 
A search on "cascading combo box" produces 64 threads.

RichM
 
Hi Rich,

Yes I have been through them all, maybe I am missing something very basic, I have created the database as suggested above county has County-Num and County in, States has States-Num and States in, and Junction has Sates-Num and county-Num in, both other database has the data in them, but I cant find and database file on your system based on this setup to try and copy the functions from, Sorry to be a pain.

Best regards from Alan
 
Multiple Tables

Hi,

Interesting fact, if the County-Num has more than one number of the same value eg:
1 Luce
1 Iron
Then you cant make the Num feild a primary key.

Best regards from Alan
 

Users who are viewing this thread

Back
Top Bottom