Grouping training request database

[Guest]

I am trying to find a way to group a training request database. For now
I have 82 records with 31 fields. Each field representing a course,
with following values: 0 = not interested, 1 = 1st priority, 2 = 2nd
priority and 3 = 3rd priority.


I have to group records having similar request patterns into training
events of approx. 7 records (participants). The 7 having similar
patterns will receive a training package consisting of their common
requests, i.e. Course number 10, 6 and 14.


I want to build a query that can look for the above mentioned patterns
and group the database records into 12 subgroups.


I have tried quite a few sorting techniques with little success. The
below sample seems to be in good sorting order (except for the 1st
record). However, the farther down, the more records does not get
sorted well enough to make a pattern, since course requests with higher
numbers does not get proper sorting order. I even tried manual sorting
but the database is too complex - even more so when adding up another
hundred records.


I hope there is somebody here that could share some light with me!


Here is a condensed sample of the database:
ID# Course request
6090 10-14-15-19-21-25-26-27-
8115 1-2-3-4-
1640 1-2-3-4-10-11-12-13-14-15-16-27-
1340 1-2-3-4-6-10-11-14-15-16-19-21-
9995 1-2-3-4-6-9-10-13-14-15-16-21-


The database in raw format is like following excerpt:


ID# 1 2 3 4..... 10......
6090 0 0 0 0.... 2....
8115 1 1 1 1.... 0...
...
The ID# in records represent employees. I have redisigned the base table
into two separate employee / courses tables. The two tables are linked with
Employee number as key field (the one called ID# in sample above). I also
rearranged the raw data into a third table for course-employee, see sample
below:


EMPID CRSID PRE
7845 1 1
...
890 1 2
...
6700 1 3
...
1630 6 3
5088 7 1
...
Now how do I proceed to get this table grouped by similar request
patterns? Any ideas?

Thanks!

 

[Joseph Meehan]

I am trying to find a way to group a training request database. For now
I have 82 records with 31 fields. Each field representing a course,
with following values: 0 = not interested, 1 = 1st priority, 2 = 2nd
priority and 3 = 3rd priority.

I am sorry, but I can't quite grasp your data needs. It does sound
like you need to normalize your tables, but I am not sure.

Rather than tell us what table structure you now have, how about telling
us what the data needs are.

Like this: Need to record student request for training and prioritize
them. Also need to create list of training sessions based on the request
using .......... (a mathematical or logic formula/staement) .

 

[Guest]

I am sorry, but I can't quite grasp your data needs. It does sound
like you need to normalize your tables, but I am not sure.

Rather than tell us what table structure you now have, how about telling
us what the data needs are.

Like this:

Need to record student request for training and their priorities. Also need
to create list of training sessions based on the request.

Training sessions needs to be a mix of at least 4 equal course requests.
This is due to travel and lodging costs.

Any clearer now?

(a mathematical or logic formula/staement) .

 

[Joseph Meehan]

Need to record student request for training and their priorities.

Requested training is easy to understand, but priorities are a little
different. Are the students putting the request in a simple order of most
to last or are they using a number rating (maybe 1-10) what happens if they
request 15 and only have 1-10 for ratings? Maybe they choose or want to
choose two rated #1?

Also need to create list of training sessions based on the request.

Based on what criteria? Are you just going to list the courses that
have been requested or are you going to need to have more than one session
of some courses? Are you going to need to account for scheduling where a
student may need to be in courses held at different times. Do students have
the ability to black out times for say vacation or job duties?

Are you going to do any follow up with who attends what course if they
complete it satisfactorily when they attended etc.??