Hi Ross
I have a table with all my employees, table with work codes, a table with
all my contractors and a table with all my jobs. I want to be able to have my
work codes and jobs relate to my employee table and my contractors table
relate to my jobs table. I'm so lost and confused
You probably need more tables.
Each "Entity" - real-life person, thing, or event - should have its
own Table. You need to turn off the computer, get a pencil and a pad
of paper, brew a nice cup of tea or coffee or your preferred
(non-intoxicating for now, save that for later!) beverage, and
identify your entities and how they are related.
You'll certainly need a table of Employees; a table of Contractors; a
table of Jobs and so on. Then you must ask - what is the relationship
between a Job and a Contractor? Does each contractor do one and only
one job? or does each job potentially have multiple contractors? If
you have a many to many relationship (as seems likely - each job might
involve several employees and each employee might work on several jobs
over time) you need another table to model the relationship. For
instance you might have tables like
Jobs
JobID Autonumber Primary Key
JobDescription
<information about the job as a whole>
Employees
EmployeeID Primary Key <maybe the Social Security Number or other
unique Employee identifier, maybe an Autonumber>
LastName Text
FirstName Text
<other bio fields>
JobAssignments
JobID << Long Integer link to Jobs
EmployeeID << link to Employees
<other info about THIS employee on THIS job, e.g. a Role field with
values such as "Foreman", "Auditor">
John W. Vinson[MVP]
