Restricting Data Entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a database that records training modules completed for
employees.
There will be 5 modules with different information recorded for each module
but in saying that, some of the fields are replicated in some of the modules.
What I need to do is restrict data entry so that module 1 must be completed
before data can be entered into module 2. Module 2 is completed before data
can be entered into module 3 and so on up to module 5.
I have created a table with each candidate’s details including a personal ID
as a primary key. I then created a table for each module with a relationship
to the personal ID but this is where I came un-stuck, as I can’t figure out
how to restrict the data input until the predecessor module is completed.
Am I heading in the right direction by using a table for each module or
should I contain all data for each module in a single table.

Any guidance is appreciated.

Nick
 
Obviously you need a column for each person called 'NextModule' which should
initially be zero. To get the next module, you simply add one to this value.
Your data entry form needs to do a Dlookup on this column and present the
data entry as appropriate for the next module.
You dont say if there will be multiple courses, if so, that complicates the
design as you will then need a 'next module' column for each course not just
for each person.

Dorian.
 
mscertified said:
Obviously you need a column for each person called 'NextModule' which should
initially be zero.
Click to expand...

"A column for each person"? That is not good. You need a separate
record in this table for EACH person not columns for Person1NextModule,
Person2NextModule etc.

Even when this design flaw is corrected, I don't consider yours the
obvious solution or indeed a complete one e.g. how would you prevent an
illegal transition from Module1 to Module3?

For ideas, see

Transition Constraints
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko35

"A declarative way to enforce Transition Constraints is put the state
transitions into a table and then reference the legal transitions."

Jamie.

--
 
Back
Top