how to limit possible values

[Kamen.r]

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!

 

[Klatuu]

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 structure:

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.

 

[Kamen.r]

Many thanks Dave! I appreciate your fast response.
Kamen

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 structure:

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

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!