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