View Single Post
  #3  
Old December 11th, 2009, 07:56 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Design Question. It works now...but how to redesign it?

Some suggestions --
1. Personel data - information that NEVER changes - MemberID Autonumber
primary key, SSN, DOB, Gender, FName, Middle, LName, Remarks (If by chance
LName changed)
2. Assignment - AsgID Autonumber primary key, MemberID long interger foreign
key, MOS, Rank, Position long interger foreign key, AsgDate, XferDate, etc.
3. Position - PosID Autonumber primary key, Title, etc.
4. Training - TrainID Autonumber primary key, Title, Level, Recur long
integer, Period (M- month, Q- quarter, YYYY- year, W- week), Slip 'S' or 'C'
(next required from last scheduled or last completed)
5. PositionTng - PosID long interger foreign key, TrainID long interger
foreign key
6. TngHistory - TngHisID Autonumber primary key, MemberID long interger
foreign key, TrainID long interger foreign key, ReqDate DateTime, CompDate
DateTime,

Query to create TngHistory record -- MemberID, TrainID, XferDate null,
ReqDate = IIF(Max([ReqDate]) Is Null, Date(), DateAdd([Period], [Recur],
IIF([Slip] = "S", Max([ReqDate]), Max([CompDate])) )

Max([ReqDate]) based on having past record for individual for this TrainID.
If not past record then Date() as ReqDate.

--
Build a little, test a little.


"Kimberly3626" wrote:

Yet again, I'm in way over my head! I'm going to simplify the current
situation to make it easy to understand (and communicate), and hopefully I
will be able to do what I intend to do without crashing everything that I
currently have!

I have a training management database. It pretty much automates my position
for the guy taking my place. I have it set up to tie various training
requirements to the positions in the unit. The problem: Lets say someone
gets promoted. They move positions. As a result, all of the completed
training (tied to the position itself) must be reset to zero and reentered.
In the event that they then move on to ANOTHER position where some of the
previous training now counts again, I have lost all record of that original
training that they did in their first position. My solution: Tie the
training REQUIRED to the position. Then tie the COMPLETION DATA to the
individual!

Currently:
1. Table of personnel data. Unfortunately, being a military unit, SSN is
the PK. This table assigns a person to a position.
2. Table of Training. This ties the individual courses to the position.
Training ID is the PK. This includes the fields for 'Enrolled' and
'Completed.'
3. Tables are joined by relationships - one-to-many from the personnel data
Position field to the training Position field.
This works outstanding! Aside from having to reinvent the wheel every time
a person moves! (which happens more often than you'd think)

My Goal:
1. Table of Personnel Data. This still assigns the person to a position.
PK is still SSN. We'll call this table 'PD.'
2. Table of Required Training. This is designed the same as above, minus
the 'enrolled' and 'completed' fields. PK is Training ID. We'll call this
table 'RD.'
(Note: My relationship - one-to-many from PDPosition and RTPosition works
great to generate what training is required for each position!)
3. Table of Completed Training. This table will include all of the training
that an individual has completed throughout their lifetime on the team. Not
all training will be required for all positions, but I don't want to lose the
history of what they have accomplished. But this table is my nemesis!!! PK
is Training ID (no relation to Table of Required Training's Training ID
field). We'll call this table 'CT.'

While I got the Query between tables 1 and 2 to work great, I need this
monstrosity to do the following:
Identify the position that a person is assigned to. Pull the required
training for that position. Then, pull the completed and enrolled data for
ONLY the courses that are required for the position.
In short, a query design would look something like this: (which doesn't
work! It pulls all of the training, regardless of position, and assigns it to
the person)
PD-SSN...PD-Position...RT-Course Title...CT-Enrolled...CT-Completed.

What I can't make it do: Ignore the extra training that a person has done
that is also in the CT table!

Thanks in advance.
Kim