help with table arrangement

[NewAccessUser]

hi,
i'm new to access and have a question with regards to how i should setup my
tables.

i am creating a database of the training that's available to employee's in a
company. there are over 100 employees and about the same about of training
courses available to these employees. i want to setup a database that will
enable me to quickly be able to tell which employee's have created which
training courses also be able to search by employee.

any help would be greatly appreciated,

thanks

 

[Tom van Stiphout]

On Thu, 8 May 2008 19:23:01 -0700, NewAccessUser

This is a classic many-to-many relation: each employee takes many
trainings, and each training is given to many employees. The design
would be something like this:
tblTrainings
TrainingID PrimaryKey (=PK), Autonumber
TrainingName UniqueIndex

tblEmployees
EmployeeID PK, Autonumber
FirstName
LastName

tblTrainingsTaken
TrainingID PK
EmployeeID PK (yes, the PK is over both fields)
DateTaken
(this table is often called a Junction table)

So far all fields above are required.
Go in the Relationships window and draw relations between the ID
fields, and enforce them.

The way you implement this in the forms: take a look at how the
Northwind sample application does it. It has a many-to-many between
Orders and Products, with OrderDetails as the Junction table. Then see
how the Orders form allows you to select Products. Translated to your
situation you could have an Employee form allowing you to select
Trainings, or a Training table allowing you to select Employees.

Happy programming,

-Tom.

 

[KARL DEWEY]

A couple of mote things to consider. Some training must be repeated on a
calendar basis and other taken once. Some training is mandatory and other
electable.
So I suggest adding fields to the
tblTrainings
TrainingID PrimaryKey (=PK), Autonumber
TrainingName UniqueIndex
Recur - number - integer - number of months, weeks, or years to repeat
training
Interval - text - W- Week, M- Month, Q- Quarter, YYYY- Year used in DateAdd
function with Recur to establish next training requirement.

tblTrainingsTaken
TrainingID PK
EmployeeID PK (yes, the PK is over both fields)
DateTaken
DateSched - used to calculate next training if based on schedule plus Recur
times interval instead of when last taken.

If Recur has a 0 (zero) then the training is not required and query not
generate a new tblTrainingsTaken record.