Automatically filling in a field

[GeoffreyB]

I am trying to set up a travel database that stores the user's country
preference and state preference. I have a similar set up for both the
country and state preference (see below) although I only provided the
country design

I currently have three tables CountryTable, CountryTableLink, and
MasterTable. The tables have the following fields:

CountryTable.CountryID
CountryTable.CountryName

CountryTableLInk.CountryLinkID
CountryTableLink.CountryID (related to CountryID above with the lookup
option)
CountryTableLink.CustomerName (related to TableName in MasterTable)

MasterTable.CustomerName

I want to be able in a form to specify MasterTable.CustomerName and
have this populate the CoutnryTableLink.CustomerName field for each
record that I create in the CountryTableLink table. In theory I want
both the state and country tables with one MasterTable.CustomerName so
that I can store what each Customer's country and state preference is.
I can do this easily in a table based on the relationships but am not
sure how to design a form that does the same thing. Thanks

Geoffrey

 

[Al Camp]

GeoffreyB,
I think your design needs a bit of tweaking.

CustomerTable... (the ONE side of the realtionship)
CustomerID CustomerName CountryID
154 J Jones 7
731 B Smith 12

Country Table... (the MANY related to Customers by CountryID)
CountryID CountryName
6 England
7 France
10 Italy
12 Germany
18 UnitedStates

StateTable... (the MANY related to Countries by CountryID)
CountryID State
18 Texas
18 New York
12 Bavaria

There should be no need for a "connector" table to relate Customer to preferred
Country or State.
Let's start there...

 

[GeoffreyB]

Thanks for the tips Al. Do you have any design tips for if I wanted to
allow the customer to have multiple country preferences as well as
multiple state preferences? Thanks

Geoffrey

 

[Al Camp]

Geoffrey,
You'll need 2 more tables. CustomerCountries and CustomerStates. These tables would
contain multiple entries in either catagory against a customer.
Customer is the ONE realtionship, CustomerCountries and CustomerStates are the MANY
tables.
So, one each customer record, you'll need two subforms to allow multiple Country State
entries agaianst each customer.