Query to crosstabulate data (1 Viewer)

foxtrot123 · Jun 20, 2013

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

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.

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:

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?

JHB · Jun 21, 2013

Create a table with number from 1 to 100, call the table and the field "Numbers". Then create a query with the following, call it Query2:

Code:

 SELECT SortingData.SubejctID, Numbers.Numbers, SortingData.StatementID, IIf([numbers]=[statementid],1,IIf([PileID]=[Numbers],1)) AS [Match] FROM Numbers, SortingData ORDER BY SortingData.SubejctID, Numbers.Numbers, SortingData.StatementID;

And at last another query:

Code:

TRANSFORM First(Query2.Match) AS FirstOfMatch SELECT Query2.Numbers, Query2.StatementID FROM Query2 GROUP BY Query2.Numbers, Query2.StatementID PIVOT Query2.SubejctID;

Query to crosstabulate data (1 Viewer)

foxtrot123

Registered User.

Attachments

JHB

Have been here a while

Similar threads

Users who are viewing this thread