Need help with database design

[cm500]

I'm very new to databases so bear with me. What I need is a way to
track the training for the employees at my firm. I have 40 classes
that I will be teaching on various subjects and various levels of
difficulty. I have 100 employees that will need to take some of the
classes offered (already determined who needs to take what). I need a
way to track the training by class (did everyone who was supposed to
take the class, actually attend?), by student (have they each taken
what they are supposed to, what do they have left to take?) AND I need
to capture notes about each student for each class they take as well
as an overall student note. This database will then be a part of
performance reviews and effort at improving.

Any help at all would be appreciated, even pointers to an already
produced version of what I'm looking for. I don't have to master
Access, just get this done.

Thanks,
...Chris

[Danny]

I recommend a 'class' database with descriptive fields about the classes
and also a student database with descriptive fields pertainint to each
person
then a master table that will duplicate each person for every class they
take and this table will have descriptive fields about the class, did they
actually attend, start finish, dates, the memo field can be the note.
Create a nice front end with use of the main menu form. Buttons to add,
delete, modify class
add, delete, modify student
add delete modify classs info

what do you think?

"cm500" <cm****@yahoo.c om> wrote in message
news:7e******** *************** ***@posting.goo gle.com...

I'm very new to databases so bear with me. What I need is a way to
track the training for the employees at my firm. I have 40 classes
that I will be teaching on various subjects and various levels of
difficulty. I have 100 employees that will need to take some of the
classes offered (already determined who needs to take what). I need a
way to track the training by class (did everyone who was supposed to
take the class, actually attend?), by student (have they each taken
what they are supposed to, what do they have left to take?) AND I need
to capture notes about each student for each class they take as well
as an overall student note. This database will then be a part of
performance reviews and effort at improving.

Any help at all would be appreciated, even pointers to an already
produced version of what I'm looking for. I don't have to master
Access, just get this done.

Thanks,
..Chris

[Justin Koivisto]

cm500 wrote:

I'm very new to databases so bear with me. What I need is a way to
track the training for the employees at my firm. I have 40 classes
that I will be teaching on various subjects and various levels of
difficulty. I have 100 employees that will need to take some of the
classes offered (already determined who needs to take what). I need a
way to track the training by class (did everyone who was supposed to
take the class, actually attend?), by student (have they each taken
what they are supposed to, what do they have left to take?) AND I need
to capture notes about each student for each class they take as well
as an overall student note. This database will then be a part of
performance reviews and effort at improving.

Any help at all would be appreciated, even pointers to an already
produced version of what I'm looking for. I don't have to master
Access, just get this done.

*disclaimer*: I am a web developer, so this may not fit well into an
Access database without a few advanced forms and/or controls...

At first glance, I think I would handle it as so:

classes
id (auto number - PK)
name (text)
description (memo)
start_date (date)
finish_date (date)

employees
id ([auto] number - PK)
fname (text)
lname (text)
notes (memo)

idx_emp_classes
employee_id (number)
class_id (number)
notes (memo)
attended (yes/no)

Don't know if access supports it or not, but the PK for idx_emp_classes
would be set in MySQL like "PRIMARY KEY (employee_id, class_id);" Which
prevents records with the same employee_id AND class_id from being
entered into the table, but allows multiples of either one.

When populating the database, if employee 12 needs to take classes 3, 6,
7, 8, 12, then the following would be entered into the idx_emp_classes
table:

INSERT INTO idx_emp_classes (employee_id, class_id) VALUES (12,3),
(12, 6),
(12, 7),
(12, 8),
(12, 12);

To see what classes are needed for employee 7, use:

SELECT classes.name, idx_emp_classes .attended FROM classes LEFT JOIN
idx_emp_classes ON idx_emp_classes .class_id = classes.id WHERE
idx_emp_classes .employee_id = 7;

This would return the name of all the required classes for the employee
with id 7, and whether or not they attended.

To see what employees are required for class 3, use:

SELECT employees.fname , employees.lname FROM idx_emp_classes .attended
FROM employees LEFT JOIN idx_emp_classes ON idx_emp_classes .employee_id
= employees.id WHERE idx_emp_classes .class_id = 3;

This would return the name of all the employees who are required to
attend class 3, and whether or not they attended.

--
Justin Koivisto - sp**@koivi.com