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 
