Training Database (1 Viewer)

[kannon8833]

Hello. I am preparing a "simple" training database. I want to do this smartly and not duplicate data.

My first table is the list of employees and their information. I have set a primary key (PK) up for each employee.

My second table is a list of training courses - title, description, start/stop time, credits, ... Each of these courses has a unique number (PK).

It's the final database table that is driving me nuts. I want it to show whether a n employee has taken/will take/not planning to take a course. My idea is to have a status field and then wanted to link that with a student number and a course number from the other tables.

In the past I would just have put common data fields StudentName from one table and CourseName from the other into the final database tables as a means of linking them. But I thought/read it's better to use a PK instead. Well when I make the third table up I get all of this Type Mismatch errors.

I am really trying to be wiser in my database design. In past I brute force them - they work but not efficiently.

If anyone knows a website/link/template I could use to model and learn - be appreciated.

Thanks in advance to all of these smart Access programmers.

 

[StarGrabber]

Hi kannon8833,

I think you should use a m:n (many to many) relationship between the tables. To achieve this, a third, the linking table is necessary (see file attached).

And here are some links:

http://www.databaseanswers.org/
http://www.databasedev.co.uk/
http://allenbrowne.com/tips.html

 

[Cronk]

At a minimum 3 tables are required, tblPersons, tblCourses, tblPersonCourses. The latter will contain PersonID and CourseID at least, but maybe also the date(s) of training.

If you want to schedule courses with trainers and venue information, a fourth table tblCourseSessions will be required.

 

[kannon]

Stargrabber - the example you sent was very helpful. Basically the extra table with the IDs from the other tables provide the linkage.

Question - the relationships seem to be all connected yet I cannot make a form with Employee names and the courses they completed (cuts across multiple tables). The relationship takes everything goes thru tblEmployeeCourses.

Is my problem because it is a one to many relationship? I am trying to show one employee taking multiple courses and the status of each course (taken or planned),

Thanks

 

[StarGrabber]

You bet you can!

If an error message appears when adding records to the subform, please have a look to the "default value" property of the textbox "txtEmplID". Maybe you have to replace "[Formulare]!..." by "[Forms]!..."

 

[kannon]

Picture is worth a million words!!

Finally seeing how the Row Source, Control Source, Bound Column play together with the queries.

I am keeping that file for a long time!!

Many thanks StarGrabber!!

 

[StarGrabber]

You are welcome!

Good luck with your now less "simple" but obviously working training database.