How to connect info from two tables

[BADebbie]

LOL. I wish it stood for Bad Axe. I have been working with Access for 17 yrs, but I never really had to do this. I have been watching video's for two days and I know what they are saying but not able to do it. I have my tables, but I'm not sure how to get them to do what I need them to do. I have two tables, one is a list of employees, and the other is a list of equipment. Each employee has to be signed off on each piece of equipment by a certain date. First, I don't know what table to put this date in or if I should create a 3rd table for just the date. I have tried that, but it didn't work. This is what I want my end result to be. A report for each employee with each piece of equipment and the date they need to be signed off on it by. I don't even know if this is possible. When they do get signed off on it, I will then have to go back in and change that date by adding 1 year. i.e. sign off by 9/2/15 will change to 9/2/16. Do you think it is possible that I could create such reports?

[orange]

As Steve mentioned, it seems you need 3 tables (I'm guessing a little at the requirements).

You have Employees, and Equipment. Each Employee has to be Certified on a Piece of Equipment. The Certification is valid for a period of time (1 year).

tblEmployee
EmployeeID PK
EmployeeFirstName
EmployeeLastName
other employeeinfo

tblEquipment
EquipID PK auto
EquipName
other Equip Info

tblEmpCertifiedOnEquip (junction table)
EmpID FK to tblEmployee
EquipID FK to tblEquip
CertificationDate Date/Time
CertificationComment " possible field for comment or note

You can have
-a composite PK of EmpID EquipId and CertificationDate OR
-( an autonumber PK EmpEquipCertificationID and
a composite unique index to prevent duplicates composed of
EmpID EquipId and CertificationDate.)

Table relationships:
tblEmployee---->tblEmpCertifiedOnEquip<---tblEquipment

You could model this set up and test it with some test data (pencil and paper is fine).

Post back if there are questions. Note, I may have mis-guessed your requirement(s).

[June7]

The suggested structure presumes record is added to tblEmpCertifiedOnEquipment at the time employee completes certification. Or enter a record for every employee/equipment combination and leave the CertificationDate field empty until certification takes place. Either way, determining which employees have not been certified or whose last certification is expired can be derived through queries of these tables.

Now the question is do you want to maintain history of certifications? Or will you have just one record for each employee for each equipment and edit the CertificationDate value?

[BADebbie]

I would like to just edit the certification date. I can add new employees or equipment but I just want something that I can go into monthly and update with new dates after they get certified.

[BADebbie]

All that should work, as long as I set up the Junction Table correctly. I better watch a video on that. thank you.

[orange]

Good stuff! Post back if you have any questions or issues.