Grouping training request database

[Peter]

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...
...


Thanks!

 

[Guest]

You did not say what the 82 records represented. I assume each record is for
employee/student. You should not have a separate field for each course.
You should have a table for employee/student. Another for courses with
fields that describe the course like name, lenght, instructor, required, etc.
A third table Course-EMP would have employee/student ID, CourseID,
preference (0, 1, 2, 3).

 

[Peter]

- Thanks, Karl, and yes, the records do represent employees. I
redisigned according to your suggestion 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 now 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!