how do I assign more than one position to a person?

J

Jarrod

I have a database for my community service organisation, but several members
of the group have more than one position in the group. How can I show this
in the database
 
A

Allen Browne

Create a related table.

You will have:
a Member table, with a MemberID primary key.
a Position table, with a PositonID primary key.
a table of MemberPosition table.

The 3rd table will have fields:
MemberID who this entry relates to
PositionID what position this member holds.
StartDate when this member commenced in this position.

For the interface, you will have a main form bound to the Member table, with
a subform bound to the 3rd table. The subform will be in continuous view, so
you can add as many positions as apply. In the subform, you will have a
combo box that uses the Position table as its RowSource, so you can select a
position from the combo, on as many rows as you need.

Tehnically, the 3rd table is called a junction table, and this is the
standard way of resolving a many-to-many relation into a pair of one-to-many
relations. For another example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
 
G

Graham R Seach

Jarrod,

In addition to Allen's excellent advice, you might also like to consider
using roles instead of user accounts. Each person assigned to a role would
access the database using the same userid and password. I don't know if
that's appropriate in your scenario, but it may be worth considering.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Top