new training database

[Guest]

Hi Al

I have been asked to setup a new database for staff training..It doesnt have to be majorly complicated but i thought i would ask your advice. My first project has some aspects that aren't normalised so i thought before starting another one i had better learn how to do it the correct way

The object of the database will be to enter an employee name and find out what training they have received and on what dates

I am thinking that the tables should be something like

Table
StaffNo - Primary Ke
StaffFirstNam
StaffSurnam
StaffAddres
StaffLocatio
StaffTelN

Table
CourseNo - Primary Ke
CourseTitl
CourseDescriptio
CourseDat

Okay! thats about as far as ive got!.... i will create a form for each of these so that the information for each can be entered

Ideally i would like to be able to select an employee no and it will populate a form with the personal details on and also what courses they have taken

Also, i would like to be able to select a course no and find out which employees attended that course.

Any help would be appreciated

Matthew

 

[Jeff Boyce]

Matthew

It sounds like you have a "many-to-many" relationship between those two
tables. To track "who does what", you'll need a third table. This table
would contain a pointer to a person and a pointer to a course, plus any
fields related to "person-takes-course" (e.g., date taken).

 

[Guest]

Hi Jef

thanks for the reply. as yet, i havent created any relationships. Im kind of stuck between what happens now...

there will be lots of courses that lots of employees have taken. I'm not at all sure what sort of fields need to go into the next table and how to get the results that i need

I'm a bit flumuxed!

 

[Jeff Boyce]

Please re-read my response. The third table I suggested only needs a
minimum number of fields, as you are only record "valid" pairs (THIS
employee and THAT course), plus any info specific to that pair.

Someone who signs up for 30 courses will have 30 rows in this third table.
Someone who signs up for one will have one. Someone who hasn't signed up
for any won't have any.

 

[DDM]

Matthew, lemme get you a little farther along. I'm gonna be guided by two
points you made in your original post: (1) the database doesn't have to be
majorly complicated and (2) the object is to be able to find out for each
employee what training s/he has completed and when.

OK, you have an Employee table and a Courses table. As you say, there will
be lots of courses that lots of employees take. As Jeff Boyce said in his
reply, you need a "who does what" table, a table that brings a specific
employee and a specific course together on a specific date. Such a table
might go like this:

Table 3
ID - Primary Key
StaffNo - FK
CourseNo - FK
CourseDate

This table is to be linked to your Table 1 in a one-to-many relationship on
StaffNo, with Table 1 on the "one" side. It's to be linked to your Table 2
in a one-to-many relationship on CourseNo, with Table 2 on the "one" side.

Note that the CourseDate field moves from Table 2 to Table 3, because
according to (2) above, we're interested in when the employee took the
course, not when the course was offered (you could even rename it
DateCompleted).

Set up the tables and the relationships, then just to see how it all hangs
together, seed your Tables 1 and 2 with some sample data, then to show that
some employee completed some training on some date, create a record in Table
3. Enter a StaffNo from Table 1, a CourseNo from Table 2, and a Date.

Then try creating some queries based on the three tables to see if you can't
get the results you're looking for.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com

Hi Jeff

thanks for the reply. as yet, i havent created any relationships. Im

kind of stuck between what happens now....

there will be lots of courses that lots of employees have taken. I'm not

at all sure what sort of fields need to go into the next table and how to
get the results that i need.