One person belonging to zero, one, or more "Teams"

[sagamw]

Let's say we have a table with people data, primary key PEOPLEID.
We make another table (I dont want to use the term group) TEAMS.
Every person in the people data table could belong to a TEAM or more than one or all or none.

My first problem is how can I deal with this in tables/fields level? I mean, I have the record of a person: What fields I must create to store the TEAMIDs in which this person belongs (from none to all)?

My Second problem is how can I deal with this in form level? i.e. how the form looks like to be able from a person's record to add him or remove him from a team?
(Mind that the number of the teams could change any time and can be 10, 20, or more, so checkboxes is not an option I think).

 

[dhookom]

You create a junction table that has the PEOPLEID and TEAMID fields. This becomes the record source of a subform. You would set the Link Master/Child to the PEOPLEID field and show a single combo box in the subform that is bound to the TEAMID field.

Duane
Hook'D on Access
MS Access MVP