Creating linked Sub-forms (1 Viewer)

[jontait]

Hi, I'm very much a beginner with Access 97 so please be patient!

I'm creating a database to show skills, knowledge and training of employees. I've got two tables, one called EMPLOYEES and the COURSES. The fields are as follows:

EMPLOYEES
EmployeeID
Name
Telephone
JobTitle

COURSES
CourseID
CourseName
Date
Certificate

Now, one employee could go on several courses, and each course could have several employees on it. I've created a "link table" between the two tables called EMPLOYEECOURSE.

EMPLOYEECOURSE
EmployeeCourseID
EmployeeID
CourseID

and then linked (via the relationships) the EMPLOYEE table to the COURSE table via this new table...make sense so far??

What I'd like to be able to do is create a form for the user to enter employee's details then to, by way of a subform, to enter the courses he/she has been on. I assume I'd enter the course details in a seperate form, then link somehow in the subform only the courses that particular employee has attended...but how??

I'm sure its really easy but I'm pulling my hair out here, any help will be greatly appreciated.

Regards

 

[Kraj]

It's been awhile since I worked in '97.... I assume there is no wizard when you create the subform? If there is, it can help you determine the correct link.

Otherwise you'll have to go into the subform properties.

I don't imagine it would be too tough to create a subform on the link table that uses the main form's employee ID, with a combo box to select a new course. You can use VBA code to refresh the subform each time a course is added to the user can see the courses and continue to add new ones.

 

[Len Boorman]

Suggest at this stage 3 forms

Employee Details

Course Details

CourseEmployee record

Suggest not using sub form because once employee exists then only necessary to assign to course.

also

EMPLOYEES
EmployeeID This is primary key I assume
Name
Telephone
JobTitle

COURSES
CourseID This is primary Key I assume
CourseName
Date This goes in EMPLOYEECOURSE and is date that employee attended that course
Certificate

EMPLOYEECOURSE
EmployeeCourseID This is not required at all
EmployeeID This is Primary Key and Foreign Key
CourseID This is primary key and Foreign Key

Had to rush a bit so hope this helps

Post back if you have more questions

L

 

[jontait]

Len

Thanks very much for your help, I've made the changes you suggested. However,

What do you eman by primary and foreign key? I can make just one field a primary key within the tabel, how do I make both primary and how do I make a field a foreign key?.....sorry but I REALLY am a beginner.

I've attached how my relationship diagram now looks, is that right?

I still don't see how I would tell Access which course each person has been on, basically I want the database to keep a record of all the training each employee has had over their time with the company.

Once again, thank you for your assistance

 

[MikeFabro]

You can select multiple primary keys in one table by holding down ctrl (or shift) to select multiple fields before hitting the little key icon. A foreign key is a primary key from another table -- so in your joining table (Employeecourse), the two ID fields make up the primary key for that table, but each ID field is a primary key in either the Employee or Course table.

If you want to be able to store multiple courses (with the date the course was taken) for each employee, you must make the Employee to employeecourse relationship a one-to-many relationship.

Once that relationship has been made, your relationship table can look somthing like this:
say you have Bob (employeeID = 1), and Janet (enployeeID = 2)
and the courses Access 101 (CourseID = 101) and Eating 201 (CourseID = 201).
and say Bob took both courses, and Janet only learned how to eat -- then your joining table (EmployeeCourse) would look something like this:

Employee ID | Course ID | Date
1 101 2001
1 201 1983
2 201 1976

-MF

 

[jontait]

Thanks Mike

Thanks Mike...this forum is a gold mine!

I've created the links etc as you've said, I've attached the new relationship table, have I done it right?

I'd now like to be able to set up a form whereby the user is presented with a form to browse/enter employee details and within that form it will display a list of courses he/she has attended and when.

I've already got a form for employees and courses, where I assume I enter each employee and course into different forms, but I can't see how I could get a sub form (within the employee form) showing a list of course the employee being shown has attended. Then for this to change as you move through the employees.

I know that you can use Lookup wizards in tables/forms but would that be applicable in this instance?

Thanks so much for all your help, much appreciated. I'm keeping a database of those I owe a beer to!

 

[MikeFabro]

Your relationships are now right - good job.

To make a form that incorporates the two forms that you've already created, a subform can be used. The form containing course information would be the subform on the form with the employee info. I had to create something just like what you're doing, and I posted for help right here (just like you've done), and someone referred me to this post: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

If you look at PAT HARTMAN's second post, he put up an example of a database that uses many to many relationships (the one you've created between the two tables is a many to many relationship) - and you can see what he's done with the forms/subforms to display both sides of the many to many on one form.

-MF
*a beer would be nice right now [despite the fact that its 9:30 in the morning - lol]

 

[jontait]

Mike

The clouds are beginning to clear now and it's all starting to make sense

Thank you so much for your help, I'll magic a bottle of cold beer to your desk as we speak, you might need to increase your bandwidth to accomodate it though!

I'll have a look through the database that Pat posted as I'm sure that's what I'm after. If I can get the bare bones of it all working the rest will be easy to pull together I'm sure.

Best wishes
Jon

P.S. just after 2.30pm here so not long until hometime!!
P.P.S. Any moderators/forum managers reading this....this forum is the "dogs dangly bits"!

 

[MikeFabro]

I'll magic a bottle of cold beer to your desk as we speak,

Ha Ha - sounds good, thanks!

Ya, it is definately what you're after, and if you follow his settings/properties for the subforms he uses it'll be smooth sailing!

Glad I could help

-MF

 

[jontait]

Ha Ha - sounds good, thanks!

Hi Mike

I've done it!! I've finally got the database I was after. Thank you!

I'm having some trouble however sending the bottle of beer. I've contacted our IT guys but they assure me thats not what my network hub is for?!?

Cheers

 

[Pat Hartman]

One more thing, don't use Date as a column name. It is the name of a function and will cause problems in certain situations. Search for naming standards to find more rules.