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]