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.

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).

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?

http://i87.photobucket.com/albums/k155/iglooiguana/db1.jpg

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.

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?

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

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

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.

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?

A couple questions...

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

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.

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...

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.

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?

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

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.

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.

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?

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

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.

I see it as one-to-many because each title in the title table can appear many times in the employee table (for instance, you could have more than one supervisor). Notice that one-to-many is in the opposite direction as the one between employees and the junction table. Same with status; more than one employee could have any given status.

You've changed the subform so that the certificate is simply a textbox now. It was a combo, which is what I would have. I'd have a 2 column combo, first column is the ID field but hidden, second column is the name.

I see it as one-to-many because each title in the title table can appear many times in the employee table (for instance, you could have more than one supervisor). Notice that one-to-many is in the opposite direction as the one between employees and the junction table. Same with status; more than one employee could have any given status.

You've changed the subform so that the certificate is simply a textbox now. It was a combo, which is what I would have. I'd have a 2 column combo, first column is the ID field but hidden, second column is the name.

I can change it to a combo box but the only selection i get is the ID number not the actual text.
The subform on my form is linked to the EmployeeCertificate junction table.

It seems like I am taking 3 steps back for every step forward on this one.

It really should not be this complicated should it?

No, it shouldn't be. You need to change the SQL in the row source property to include both columns. You need to make sure the column count, width, and bound column properties are all set appropriately.

No, it shouldn't be. You need to change the SQL in the row source property to include both columns. You need to make sure the column count, width, and bound column properties are all set appropriately.

Thanks for you help pbaldy I really appreciate it.

2 things I have done with your suggestion.
ONE:
Control Source: CertificationID
Row Source Type:Table/Query
Row Source:
SELECT [CertificationTable].[CertificationID], [CertificationTable].[CertificationName] FROM CertificationTable;
Bound Column: 2

FORMAT
Column Count: 2

When I open the form the Sub Form CertificationID field now has a dropdown box and shows 2 columns; the ID and the name of each certificate.
When I select one I get the following message.
“The Value you entered isn’t valid for this filed.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.”

I don’t think is the FieldSize.
Now the text/numeric field could be a problem but wouldn’t that mean changing my tables?

TWO:
Changed the query to only show a single column

Control Source: CertificationID
Row Source Type:Table/Query
Row Source:
SELECT [CertificationTable].[CertificationName] FROM CertificationTable;
Bound Column: 2

FORMAT
Column Count: 1

This time the CertificationID column only shows the certificates but when I select one nothing happens. No data fills the field; it's just blank but I get no message at all.

Could it be that my original tables are set up wrong?

http://i87.photobucket.com/albums/k155/iglooiguana/Database/Relationships.jpg

For your first, the bound column should be 1 (the ID column), not 2 (the name column). Also, I would set the column widths to 0";1" so that the ID column was hidden from the user (or whatever width is appropriate for the second column).

For the second (which I wouldn't do anyway), the problem is you have the bound column as 2, but there is only 1 column in the combo, so...

For your first, the bound column should be 1 (the ID column), not 2 (the name column). Also, I would set the column widths to 0";1" so that the ID column was hidden from the user (or whatever width is appropriate for the second column).
...


Ok, I did that and now I have a drop down box with nothing in it. :( :confused:

Here's your most recent sample back.

Here's your most recent sample back.

Thanks for you help and patience pbaldy.
I opened your fixed copy up next to mine and I found the only difference was the column count; mine was 1 and yours was 2.

I did notice that the Rowsource SQL statement ended with "WITH OWNERACCESS OPTION;"
Is that because you opened and saved it?

Again thanks for all those who helped me.