Setting up a db field for multiple names

[waypoint]

Hi, I would be very grateful for advice as to how I can set up a relational db that may need to capture multiple users.

I am setting up a research report with a table for staff members, (another staff_id table to be used as a pull down menu), and a research activity table.

Sometimes the research activity has more than one staff member involved. I have set up 6 fields based on staff_id and at the moment I am just duplicating the same name six times. If I change the name, based on a pull down menu, in one field it changes in all 6.

I need to be able to put up to 6 different staff names against any research activity and also be able to do a search on all the staff_id fields to capture any activity for any particular staff member.

I would be very grateful for some advice please?

Heather

[RalphL]

It looks like you have a many to many relationship between activity and staff, i.e., a staff member can work on one or more activites and an activity can have one or more staff members assigned. You need a join table between the 2 tables. This table would have at least the StaffID and the ActivityID fields. A portal in activity will show staff assigned. A portal in staff will show activities assigned.

[waypoint]

Thank you so much Ralph,

I shall have to look up some more information about portals, is this a little like sub-forms in Access, where you have a main table at the top and related data (linked by relationsip) published underneath?

Kind regards

Heather

[RalphL]

Sorry, I have never used Access, but that sounds like it may be similiar.