foxtrot123
Registered User.
- Local time
- Today, 02:32
- Joined
- Feb 18, 2010
- Messages
- 57
I asked 18 people to each sort 100 statements into piles based on the similarity of the statements. The results are arranged as below.
For example:
- Bob sorted statements 1, 3, and 100 into the same pile (Pile ID = 5), and statements 2 and 4 into the same pile (Pile ID = 2).
- Mary sorted statements 1 and 100 into the same pile (Pile ID = 3).
I need to create separate summaries for each Subject.
The summary should indicate, for every possible pair of statements (1 & 1, 1 & 2, 1 & 3 ... 100 & 100), a 1 if the person sorted both statements into the same pile and a 0 if they didn't. Identical statement pairs (e.g., 1 & 1) should always get 1.
Bob's summary would look like this:
I'm assuming a crosstab query is a start, but I couldn't figure out how to set it up. Any suggestions?
For example:
- Bob sorted statements 1, 3, and 100 into the same pile (Pile ID = 5), and statements 2 and 4 into the same pile (Pile ID = 2).
- Mary sorted statements 1 and 100 into the same pile (Pile ID = 3).
Code:
SubjectID StatementID PileID
------------------------------
Bob 1 5
Bob 2 2
Bob 3 5
Bob 4 2
...
Bob 100 5
------------------------------
Mary 1 3
Mary 2 11
Mary 3 1
Mary 4 2
...
Mary 100 3
------------------------------
etc.
The summary should indicate, for every possible pair of statements (1 & 1, 1 & 2, 1 & 3 ... 100 & 100), a 1 if the person sorted both statements into the same pile and a 0 if they didn't. Identical statement pairs (e.g., 1 & 1) should always get 1.
Bob's summary would look like this:
Code:
StatementIndex1 StatementIndex2 Similarity
1 1 1 (identical statements always get a 1)
1 2 0 (Bob did not sort statements 1 & 2 into the same pile)
1 3 1 (Bob sorted 1 & 3 into the same pile)
1 4 0 (Bob did not sort 1 & 4 into the same pile)
...
1 100 1 (Bob sorted 1 & 100 into the same pile)
-----------------------------------
Now statement 2 with every possible pairing:
2 2 1
2 3 0
2 4 1
etc.
I'm assuming a crosstab query is a start, but I couldn't figure out how to set it up. Any suggestions?