Controlling Data Formatting

[Robert]

Good Morning Everyone

A bit of background first:

I created this database so that I can not only track personnel and
training, also force users to utilize a standard data entry method.
Prior to this implementation we would have 5 seperate date forms, three
ways of indicating not applicable, etc.

What I need to figure out now is a way to override some of these
settings. For example: I add a new person to my database, that person
does not require certain training items. My training items are set as
medium dates because if they do not have the training it would
naturally be blank and easily locatable. I have some personnel that do
not need to do the training. Therefore since they would not have the
date entered they would be listed as not having done it. I need to
figure out how to make this read Not applicable or somthing similar.

I hope this made sense! I will clarify as needed.

Thanks again, as always

Robert A. Wukich, Sr
Sgt/USMC

 

[Larry Daugherty]

What you need is a table of Skills, Training, ??. It will be the many
side of a one-to-many with your main personnel table. You'll create a
record for each training requirement for each individual with all of
the fields that are relevant to that instance: Course Name, Number,
Date Assigned, Date Completed, Score, Notes, ???.. I recommend that
the training components be listed in a table used as a lookup table
and that you simply have a combobox on your subform to bring in all of
the information from the lookup table.

HTH

 

[John Nurick]

Hi Robert,

A date field can only store a date (to be precise, a number that
represents a point in time) or a special value called Null (which
usually represents an unknown value).

One way around the problem is to use a special date that would not
otherwise be encountered (such as 1/1/1900) to mean "this person does
not need this training item".

But the fact that you've hit this snag means that your database
structure doesn't fit the real-world situation you are modelling.
Getting the structure right is the most important single thing in
databases. Here, it sounds as if you have something like this:
Persons
Training items
For each person,
Training items the person is required to take
Training items that the person has actually taken

One way of modelling this would be with three tables:

tblPersons
PersonID (e.g. a serial number or other unique identifier)
Name
other stuff

tblTrainingItems
TrainingItemID
Name
Description
other stuff

tblPersonsTrainingItems
PersonID
TrainingItemID
Required (Yes/No)
DateCompleted
other stuff, maybe

In this structure, a record in tblPersonsTrainingItems like this:
XXX, YYY, Yes, 04/04/2005
mean that Person XXX completed required Training Item YYY on 4 April;
XXX, YYY, Yes, Null
would mean that Person XXX requires Training Item YYY but has not
completed it; and
XXX, YYY, No, 04/04/2005
would mean that Person XXX didn't require item YYY but completed it
anyway.