Question Duplicate Fields (1 Viewer)

[Manic_Rach]

Hi guys, I was wondering if you could help me with a problem I'm having. I've created a database and I have one table named "Engineer" and this is linked to the table "Job".

"Engineer" contains the information of engineers, ie name, address, phone number etc. "Job" contains information of jobs, ie location, date etc.

On the "Job" table I need to enter what engineers are working on the job, but more than one engineer can be working on one job. I have three drop-down list fields on the "Job" table named "Engineer Name 1" "Engineer Name 2" and "Engineer Name 3".

I want to know if there is a validation rule or anything that will stop the same engineer name being selected in more than one field.

Thanks,

Rachel

 

[jzwp22]

I have three drop-down list fields on the "Job" table named "Engineer Name 1" "Engineer Name 2" and "Engineer Name 3".

Having sequentially numbered fields is a sign that your database is not normalized. Since a job can have many engineers, that describes a one(job)-to-many(engineers) relationship. Assuming that an engineer can work on many jobs that also describes another one(engineer)-to-many(jobs) relationship. Having two one-to-many relationships between the same 2 tables is represented with a junction table.

First a table to hold the basic job info

tblJob
-pkJobID primary key, autonumber
-txtJobNumber
-txtJobName

A table to hold all people including the engineers

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

Now the junction table

tblJobPeople
-pkJobPeopleID primary key, autonumber
-fkJobID foreign key to tblJobs
-fkPeopleID foreign key to tblPeople

Not the "fk" or foreign key fields should be long integer number datatype fields to match the corresponding autonumber field of the related primary key (pk) field.

In terms of making sure that the same engineer is not assigned more than once to a job, you would just set up an index on the junction table to make sure that the combination of fkJobID and fkPeopleID is unique (no duplicates)

 

[Manic_Rach]

Thank you so much, that has really cleared things up. It makes a lot more sense now

 

[jzwp22]

Glad to help out; good luck with your database.