new training database

Hi All

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 1
StaffNo - Primary Key
StaffFirstName
StaffSurname
StaffAddress
StaffLocation
StaffTelNo

Table 2
CourseNo - Primary Key
CourseTitle
CourseDescription
CourseDate

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

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

--
Good luck

Jeff Boyce
Access MVP

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.

I'm a bit flumuxed!

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.

--
Good luck

Jeff Boyce
Access MVP

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


"matthew" wrote in message
...
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.

I'm a bit flumuxed!