Fenrirshowl
Technical User
Hi guys
This is also posted in the Queries and Jet SQL forum (in case any answer involves a query, which is possible)
I am looking at MS Access 2003 for the first time in many years, and am having a bit of a trouble achieving what I am after.
I would like to create a table with a number of lookup entries where one lookup defines the available terms in the next lookup.
An example of what I am trying to achieve could be illustrated by means of location.
Say I have a
"Continent" table with entries "North America", "Europe"
"Country" table with Entries "USA", "Canada", "England", "France"
"Town" table with "Washington", "New York", "London", "Manchester", "Paris", "Ottowa"
The Town table has fields TownID (Autonumber and primary key), TownName (Text field), CountryID (Long Integer and Foreign ID linked to the Country table via relationships)
Similar for the Country table, with CountryID (Autonumber and primary key), CountryName, ContinentID (Foreign key)
And Continent table, with ContinentID (Autonumber and primary key) and ContinentName
All foreign keys in one table are linked to the primary keys in another using Relationships (on a Many-to-One basis)
How do I use these as lookup entries in my main data table which will hold a persons name, their zip/post code, continent, country and town such that
a) when Europe is selected in the continent field, the only entries shown in the country field inputs are England and France
b) and then only London and Manchester are shown in the Town field when England is the selected country.
Setting up the initial Continent lookup can be achieved in the table design part of the process, no problem. However, I cannot set up the Country field such that only valid entries based on the value of Continent are shown.
Currently I am simply working with Tables - I wanted to ensure I can get things working properly before starting work on the Forms, but I have this suspicion I might only be able to achieve my goal at the Form design stage.
Can someone please provide me with some pointers? (Or tell me that it will not work and stop trying!)
Many thanks
Fen
This is also posted in the Queries and Jet SQL forum (in case any answer involves a query, which is possible)
I am looking at MS Access 2003 for the first time in many years, and am having a bit of a trouble achieving what I am after.
I would like to create a table with a number of lookup entries where one lookup defines the available terms in the next lookup.
An example of what I am trying to achieve could be illustrated by means of location.
Say I have a
"Continent" table with entries "North America", "Europe"
"Country" table with Entries "USA", "Canada", "England", "France"
"Town" table with "Washington", "New York", "London", "Manchester", "Paris", "Ottowa"
The Town table has fields TownID (Autonumber and primary key), TownName (Text field), CountryID (Long Integer and Foreign ID linked to the Country table via relationships)
Similar for the Country table, with CountryID (Autonumber and primary key), CountryName, ContinentID (Foreign key)
And Continent table, with ContinentID (Autonumber and primary key) and ContinentName
All foreign keys in one table are linked to the primary keys in another using Relationships (on a Many-to-One basis)
How do I use these as lookup entries in my main data table which will hold a persons name, their zip/post code, continent, country and town such that
a) when Europe is selected in the continent field, the only entries shown in the country field inputs are England and France
b) and then only London and Manchester are shown in the Town field when England is the selected country.
Setting up the initial Continent lookup can be achieved in the table design part of the process, no problem. However, I cannot set up the Country field such that only valid entries based on the value of Continent are shown.
Currently I am simply working with Tables - I wanted to ensure I can get things working properly before starting work on the Forms, but I have this suspicion I might only be able to achieve my goal at the Form design stage.
Can someone please provide me with some pointers? (Or tell me that it will not work and stop trying!)
Many thanks
Fen
