A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to limit possible values



 
 
Thread Tools Display Modes
  #1  
Old July 1st, 2009, 12:03 PM posted to microsoft.public.access.tablesdbdesign
Kamen.r
external usenet poster
 
Posts: 2
Default 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  
Old July 1st, 2009, 04:28 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old July 2nd, 2009, 07:40 AM posted to microsoft.public.access.tablesdbdesign
Kamen.r
external usenet poster
 
Posts: 2
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.