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
|
|||
|
|||
1 to 5 relationship
I have 2 tables
table "Person" and table "Team" with a 1-M relationship a person can be a member of 0 or 1 team but a team can have from 0 to 5 Persons at the moment i am enforcing this with code is there any way to enforce this with a constraint on table "Team" ? |
#2
|
|||
|
|||
1 to 5 relationship
sorry, i meant
is there any way to enforce this with a constraint on table "Person" ? and not is there any way to enforce this with a constraint on table "Team" ? |
#3
|
|||
|
|||
1 to 5 relationship
Loterken,
Logically, tblTeam should be the ONE, tblPersons should be the MANY. One team, many persons... Given that a Person can belong to only one team... tblTeam PersonID should be constrained (Indexed/No Dupes) to prevent assignment to two teams. Also, the most logical form would be tblTeam on the main form, and tblPersons on a continous subform related to the Team by TeamID. If you look at it as One Person to One Team, the tblPersons TeamID can not be constrained (No Dupes) ... as other persons (in that same tblPersons) could also belong to that same team. Is that what you're trying to determine? -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Loterken" wrote in message ... sorry, i meant is there any way to enforce this with a constraint on table "Person" ? and not is there any way to enforce this with a constraint on table "Team" ? |
#4
|
|||
|
|||
1 to 5 relationship
indeed i mistyped on my original post, corrected on the reply to myself
team is the 1 side, person is the many sides so team has only 1 field (ID), the primary key (of course there are more fields) person has among others a field (TeamID), a foreign key the problem is,that allows a team to have a whole lot of persons but a team may not have more than 5 persons i solved it by not allowing the user to give the same team to more than 5 users i did that by using code in my program (vb6) i am wondering if there is a way to have a constraint on the person table that would not allow more then 5 teamID i thougt of putting 5 person fields in the team table, but that would violate 1NF |
#5
|
|||
|
|||
1 to 5 relationship
On Sat, 20 Feb 2010 13:54:52 +0100, "Loterken" wrote:
I have 2 tables table "Person" and table "Team" with a 1-M relationship Well... no. I think you have it backward. It's a many to one relationship - each Person has no more than one Team, each Team can have more than one Person. a person can be a member of 0 or 1 team but a team can have from 0 to 5 Persons at the moment i am enforcing this with code is there any way to enforce this with a constraint on table "Team" ? Teams TeamID TeamName other information about the team as a ding an sich Persons PersonID primary key TeamID foreign key biographical data about the person There's no table validation rule that will limit the team to no more than five members; when Access gets table triggers you can use a BeforeInsert trigger to detect the sixth addition and prevent it, but with Access as it is in version 2007, you need code in the Form's BeforeInsert event to count the existing number of team members and block the addition if the team is full. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
1 to 5 relationship
Check out John's response... regarding using the BeforeUpdate event
to prevent a sixth entry per team. There is no property that will limit a table to five entries per team. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Loterken" wrote in message ... indeed i mistyped on my original post, corrected on the reply to myself team is the 1 side, person is the many sides so team has only 1 field (ID), the primary key (of course there are more fields) person has among others a field (TeamID), a foreign key the problem is,that allows a team to have a whole lot of persons but a team may not have more than 5 persons i solved it by not allowing the user to give the same team to more than 5 users i did that by using code in my program (vb6) i am wondering if there is a way to have a constraint on the person table that would not allow more then 5 teamID i thougt of putting 5 person fields in the team table, but that would violate 1NF |
#7
|
|||
|
|||
1 to 5 relationship
Al,john, thank you
so i'll leave it as it is,and put a discleamer on it, that i dont assume responsability for correctnes if the users input data outside of my program since i am a strong believer that a users data is property of the user,not of the programmer,i dont like to secure a db with pasword or user level thank you guys |
Thread Tools | |
Display Modes | |
|
|