Multiple entries for 1 field?!?!

[ljjtek]

OK,
Well I have a database which has a table-JobLog, which is used to log jobs, assign it an id, and store relevant info. Each job will have more than 1 employee working on it but I can't fiqure out how to set this up!

I have an EmployeeInfo table in which the primary key is an autonumber that is a foreign field in the joblog table. But, I can't make a seperate field for each employee on the joblog because then there would have to be more than 1 primary key in the joblog table!!

What am I doing wrong here?! I am trying to make a work sched for each job, but I can't..... Hope this isn't too confusing, even I'm confused, but anyone have a suggestion?

Thanks,
LJJ

 

[Mongooses]

You could create a third table that would contain the JobID and the EmployerID. This would create the many to many relationship you are looking for without violating any normalization rules. You would, however, need to remove the foreign key from the Job table. To recap create a table that contains the Jobs and relevant information along with a JobID primary key, an employee table and a third table that contains the primary keys from the other two tables.

HTH,
JC

 

[ghubbell]

Hi LJL,

Maybe a sketch is in order:
Employee table:
EmpID
Other stuff

A job table:
JobID (AutoNumber) Primary Key.
Your Real Job Number 12345
Description
Basic details...

A Job/Employee table:
JobID as a many from job table jobID (allow dups)
EmpID as above. (allow dups)
These 2 Make the Primary Key for this table. No dups.
Maybe a JEid autonumber just to keep things in line
Details: specific. what is this employee doing on this job?

Try a sketch kind of like you see in a query or the relationships window. I do it all the time and it really helps to clear things up sometimes! Hope this'll get it going, ;-)



Gord
ghubbell@total.net