A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Design Question. It works now...but how to redesign it?



 
 
Thread Tools Display Modes
  #1  
Old December 11th, 2009, 03:40 PM posted to microsoft.public.access.tablesdbdesign
Kimberly3626
external usenet poster
 
Posts: 26
Default Design Question. It works now...but how to redesign it?

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
  #2  
Old December 11th, 2009, 07:08 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Design Question. It works now...but how to redesign it?

Kim

If I'm understanding your description, you want the completed training
information to follow the person AND their role/position, not just the
person. That would imply that you need to show Completed Training with:

TrainingID
PersonID
RoleID
... and other completed-training-related info

Are you doing this now?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Kimberly3626" wrote in message
...
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



  #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

  #4  
Old December 15th, 2009, 11:53 AM posted to microsoft.public.access.tablesdbdesign
rolaaus[_2_]
external usenet poster
 
Posts: 16
Default Design Question. It works now...but how to redesign it?

Sounds like you have quite a situation.

Here is the best solution that I can come up with, from what I understand of
your situation.

You obviously need an Person table of some sort.

You need a MOS table

You need a Training table

and a linkage table between MOS and Training, because you are likely to have
multiple MOS's need the same training.

And just to throw a monkey wrench into the whole thing, you have some
training programs that apply not only to multiple MOS's, but may also apply
to multiple ranks.

My experience, when I was in the military, was that typically you couldn't
get promoted until you fulfilled all the previous requirements. But I wasn't
closely involved with the the Personal Actions office.

I would hate for your linkage table to end up needing the same MOS and the
only difference in the record is a rank attribute, but there shouldn't be any
reason to have a rank table. You could possibly use a multi-valued field to
store the rank(s).

"Jeff Boyce" wrote:

Kim

If I'm understanding your description, you want the completed training
information to follow the person AND their role/position, not just the
person. That would imply that you need to show Completed Training with:

TrainingID
PersonID
RoleID
... and other completed-training-related info

Are you doing this now?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Kimberly3626" wrote in message
...
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



.

  #5  
Old December 15th, 2009, 04:16 PM posted to microsoft.public.access.tablesdbdesign
Kimberly3626
external usenet poster
 
Posts: 26
Default Design Question. It works now...but how to redesign it?

Sorry for the delay...got stuck on a short notice trip!

First, I have really good news! I'm actually tracking training that is
position-specific and is completely independant of the rank and/or MOS of the
individual! We have some positions that are non-MOS specific...(As for the
Rank/MOS training, I can do that manually because we don't have many switches
between MOSs or Enlisted officer. As a result, I just load all of the
required training for your lifetime in one shot and check it off as you
complete it)

One example of what I'm dealing with:
Me! I was in Position A, which required Course A, Course B, and Course C.
I moved into Position B. This position requires Course A, Course C, and
Course D.

With the training tied to the position, the way it is now, when I move into
Position B, I have to not only clear the training data for the person who was
there before me, but I also have to enter all of my data from scratch. In
addition, I lose the database proof that I completed Course B. This is only
a problem because if we are certified in one position, we can cover it on our
own team or on another across the country. Without being able to pull the
certified personnel out of the DB, I lose the ability to cross-assign people
to meet a nationwide mission.

Seemingly not hard with the above example of 4 courses, but in reality, I
manage over 24,000 hours of training for the team that is completely position
dependant!

Looking at the replies, it seems like Karl's idea might work the best, minus
the Training Table (I'll only use the position training table for now). I'll
see what I can get to work, and let you all know what pops up...

If there are any more ideas that will work with the new 'simplified'
requirements, I'd love to hear em too! Not having the MOS wrench is a huge
help to the situation.

Thanks again!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.