If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to limit possible values
I have a table with 2 fields: Team and Country. I'd like to create a second
table that has these 2 fields and some others. How in the new table to limit range of possible Teams depending on the value in the field Country? Thanks in advance! |
#2
|
|||
|
|||
how to limit possible values
First, your design needs a bit of improvement.
You should have a table for countries. It should have a field that determines the number of teams allowed. Your Team table should carry the primary key of its country record in the Country table. Your new table should carry just the Team primary key as a foreign key. Including the Country table primary key would be redundant. You already know the team's country. Here is the basic level structu tblCountry [CountryID] - PK Autonumber [CountryName] - Text [MaxTeams] - Long Integer tblTeam [TeamID] - PK Autonumber [CountryID] - Long Integer - Foreign key to Country Table [TeamName] - Text NewTable [NewID] - PK Autonumber [TeamID] - Long Integer - Foreign Key to Team Table Now, to the actual question. You will have to do this at data entry time. I would suggest you use the After Update event of the control where you enter the county to check the maximum number of teams and compare it to how many teams are already in the database. -- Dave Hargis, Microsoft Access MVP "Kamen.r" wrote: I have a table with 2 fields: Team and Country. I'd like to create a second table that has these 2 fields and some others. How in the new table to limit range of possible Teams depending on the value in the field Country? Thanks in advance! |
#3
|
|||
|
|||
how to limit possible values
Many thanks Dave! I appreciate your fast response.
Kamen "Klatuu" wrote: First, your design needs a bit of improvement. You should have a table for countries. It should have a field that determines the number of teams allowed. Your Team table should carry the primary key of its country record in the Country table. Your new table should carry just the Team primary key as a foreign key. Including the Country table primary key would be redundant. You already know the team's country. Here is the basic level structu tblCountry [CountryID] - PK Autonumber [CountryName] - Text [MaxTeams] - Long Integer tblTeam [TeamID] - PK Autonumber [CountryID] - Long Integer - Foreign key to Country Table [TeamName] - Text NewTable [NewID] - PK Autonumber [TeamID] - Long Integer - Foreign Key to Team Table Now, to the actual question. You will have to do this at data entry time. I would suggest you use the After Update event of the control where you enter the county to check the maximum number of teams and compare it to how many teams are already in the database. -- Dave Hargis, Microsoft Access MVP "Kamen.r" wrote: I have a table with 2 fields: Team and Country. I'd like to create a second table that has these 2 fields and some others. How in the new table to limit range of possible Teams depending on the value in the field Country? Thanks in advance! |
Thread Tools | |
Display Modes | |
|
|