Please help me understand this.

[voidcranium]

I have always had trouble with this and no matter how much I read I cannot get a visual picture of how to do this.
So if any of you could show me how this can be done. It would be appreciated more than you can ever know.

A company has many employees.
Each employee can have many certificates.

So how do I set up the tables and the RELATIONSHIPS(this is the part I can't understand), so when I create a form and enter the different types of certificates they all go to the right table?

This is what I have so far.
Employee TABLE
EmployeeID(PK)
EmployeeFirstName
EmployeeLastName
EmployessAddress
EmployeeCertificateOne
EmployeeCertificateTwo
EmployeeCertificateThree
EmployeeCertificateFour
etc....to
EmployeeCertificateEight

Certificate TABLE
CertificateID(PK)
CertificateName

Could someone explain in detail how I can do this?
I really would like to understand this.
I'm sure it is simple but for some reason I just can't get a visual picture in my head on how to do it.
I'm a visual learner, I have to see it work and then read how it works to fully understand how something works.

Thanks in advance.

P.S. I forgot to mention that I need DATE fields for the Employee Certificates because they expire after a certain time.
I was not sure where to put those.

 

[pbaldy]

You don't want those certificate fields in the employee table. You want a separate table with a record for each employee/certificate combination (and whatever other data is relevant, such as your date).

 

[voidcranium]

You don't want those certificate fields in the employee table. You want a separate table with a record for each employee/certificate combination (and whatever other data is relevant, such as your date).

Thanks for the info.
Something like this?

 

[statsman]

Just looking over your database, I think I see one problem.

In the Employees table you have an employee named "Joe Smith". Joe should have an Employee Number. I don't see one in the Employee table.
When Joe gets a new certificate, you would enter just his employee number into EmployeeCertificates table to identify him. This should not be an autonumber, but rather Joe's specific identification number.

You may have covered this but I don't see it.

 

[voidcranium]

Just looking over your database, I think I see one problem.

In the Employees table you have an employee named "Joe Smith". Joe should have an Employee Number. I don't see one in the Employee table.
When Joe gets a new certificate, you would enter just his employee number into EmployeeCertificates table to identify him. This should not be an autonumber, but rather Joe's specific identification number.

You may have covered this but I don't see it.

I guess I am over thinking some of this.
Instead of using EmployeeID I wanted to use DepartmentID.
Each employee has a number specific to them, that is the department number.

Joe Smith ID# is 301(DepartmentID)

I wanted to use the department ID # as the PK field.
I did not want to use an autonumber.
Does that make sense?

 

[pbaldy]

If the department ID is unique to each employee, it's fine for the identifying field. That design looks a lot better.

 

[voidcranium]

I was trying to make a form so I could enter the relevant information for each employee, but I cant get the auto form to get the information I need.

I need a form that has all the info of each employee.
I want to select which certificates each employee is certified for and the expiration date.

Can this be done with the autoform?

Thanks

 

[pbaldy]

My guess is you'd want a form/subform, where the main form has the employee's details and the subform has the certificate details for that employee.

 

[voidcranium]

My guess is you'd want a form/subform, where the main form has the employee's details and the subform has the certificate details for that employee.

I have the form with the subform in it but the subform has no information in it.
I made the subform from a query from the EmployeeCertificate Table.
So as of right now there is no information in the table.
How do I get the information to show in the subform?

After looking at my picture above. The EmployeeCertificate Table only has the ID fields, how do I get the Certificate names in the column on my subform?

 

[Colin@Toyota]

A couple questions...

Does every employee have at least one certificate?
What kind of query are you using as the basis for the subform?

 

[voidcranium]

A couple questions...

Does every employee have at least one certificate?
What kind of query are you using as the basis for the subform?

Yes, each employee will have at least one certificate.

I just used the SIMPLE QUERY WIZARD and select all from the EmployeeCertificates table.

 

[Colin@Toyota]

ok

What you might want to try (someone correct me if there is a better way!!) is going into the design view of the query, and setting the criteria of the departmentID to the name of the textbox on the form (not subform) where it is displayed. This should filter the query results for the subform to only display the certificates that the specified employee has.

Then, also in the design view of the query, right click in the upper half, and add the certificates table. Then, add the field that contains the name of the certificate so it will be displayed in the subform as well.

I hope my advice can be of some use...

 

[pbaldy]

The cleaner way to keep the subform displaying the records for the employee in the main form is the master/child links between the form and subform.

 

[voidcranium]

Colin@Toyota thanks for your help.
I tried your suggestions but I still get nothing.

The departmentID on my form is the exact same ID as in my subform.
When I cycle through the records the ID of each one changes and they match.
But the CertificateID field is blank and I can’t select or put anything in it.


I am thoroughly confused now.

Does somebody have a sample like this I can look at?

 

[pbaldy]

It would make more sense for you to post a sample of your db, as it would then be more relevant to you.

 

[voidcranium]

It would make more sense for you to post a sample of your db, as it would then be more relevant to you.

Ok here it is.

I hope I did not mess it up to much.

Access 2000 btw.

 

[pbaldy]

If you're talking about that combo box, you have its rowsource as the EmployeeCertifcates table, rather than the Certification Table as I would expect. That's why it's blank.

You might also get rid of the spaces in your object names, as they'll cause you grief in the long run.

 

[voidcranium]

Ok let me ask you this,
Did I do everything right up until i tried to put a subform on the Employee form?

Are the relationships ok?

Do I use the Relationship(EmployeeCertifcates) table as my subform table?

 

[pbaldy]

Yes, the subform would be based on that junction table. Looks fine to me.

 

[voidcranium]

A few things I have found and need some clarification on please.
A
I think my relationship between the Employees Table and the Title table is wrong.
It should not be 1 to many. An employee can only have ONE title.
Is that right?
Do I just drag the relationship between the 2 and don’t click the check boxes?
That’s the only other way it will let me do it.
Same with the EmploymentStatus relationship. I can’t get it to work either.

B
As far as the subform; I guess it is working except it does not show the Certificate Name but only the ID number for the certificate.
I need it to show the Name in a drop down box.

Anybody have anymore tips.

Thanks

I have attached an updated and cleaned up db.