You're right: there is a better way, and this was an important question to
ask.
Keep your Project table.
Simplify your Worker table so it copes with just one person.
Add a 3rd table with fields:
ProjectID relates to Project.ProjectID
WorkerID relates to Worker.WorkerID
AssignDate Date this worker was assigned to this project.
EndDate Date the worker finished working on this project.
Typically it would be named ProjectWorker.
The 3rd table then contains one record for every combination of project +
worker. So if there are 3 workers on project 2, there will be 3 records in
the table. If there are also 5 workers on project 2, that's another 5
records.
The interface will consist of:
- A main form bound to the Project table.
- A subform bound to the ProjectWorker table.
The subform would contain a combo box for selecting the worker (i.e. its
RowSource is the Worker table.), and you select multiple workers on multiple
rows.
The 3rd table is called a junction table, and it is the standard way to
resolve a many-to-many relation. Whenever you see repeating fields such as
Worker1, Worker2, ... you need a junction table instead. For another
example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<te****@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
In my Access 2000 database, how do I build an efficient schema where a
Project can have multiple workers and I need to store each worker first
name, last name, email and phone number?? So if a Project has 3
workers then I need to store all three workers information with that
Project.
Here is how I have my tables now but there must be a better way??
Project Table:
projectID - Primary Key
workerID - Foreign Key
projectRequirement
projectPriority
Worker Table:
workerID - Primary Key
worker1FirstName
worker1LastName
worker1Phone
worker1Email
worker2FirstName
worker2LastName
worker2Phone
worker2Email
worker3FirstName
worker3LastName
worker3Phone
worker3Email