adding table records to a form in a tab control

[kattz]

I have an Employee database.
It contains a lot of information but I am having trouble adding a new table and I am sure there is something simple i am missing.

In my Employee Data Table i have fields such as Last Name, First name, address, wage rate etc....

I have all of this set up in a form with tab controls.

Certificate 3 consists of different modules which each have a code, name, result and date completed. I have put this info into a table with the fields named; code, name, result and date completed so i have 20 records in this table.

When I try and link the employee data table and the certificate 3 table, i end up with over 1800 records with 20 for each employee being the 20 modules from the Certificate Table

I hope this is not too confusing, while I am now pretty good with Access, i am not up with the jargon.

It is hopefully something simple that i am missing on this Monday morning.

 

[dhookom]

Do you have a primary key field in the Employee table with a similar field in the Certificate table? This is the most basic feature of related tables.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kattz]

No, the Cert 3 table contains data that has to relate to each of the employees, the primary key in the employee data is their employee number but i can't make this a primary key in the other table,because there are only 23 records in the Cert 3 table and 81 employees.

I can give the Cert 3 Table an autonumber primary key, but even that doesn't seem to work.

Normally i would relate fields to all the employees, but if i did that, i would have 60+ fields which of course is too big.

I've tried setting it up (cert 3 table) as a subform/subreport in my form (employee data form), but everytime i update one person, it updates everyone else's with the same info. This may be a linking problem but i can't figure that out either. I don't really understand the child/master linking thing and no matter which combination i tried, nothing worked.

thanks

 

[dhookom]

You need a junction table that contains the primary key from both tables. This is just like the relationships in the Northwind.mdb that comes with Access:

Northwind          Your MDB
Order              Employee
Products           Certifications
Order Detail       EmpCerts (new junction table)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.