Help on this Query required..

  • Thread starter Thread starter Adrian
  • Start date Start date
A

Adrian

Hi,

I have the following tables:

Table #1: Subject

SubjectID | SessionID |
-----------------------------------------------------------
U0179C | DF
U0245D | FS


Table #2: ErrorCorrectionRecords

ID | AnsCorrect | TimeTaken | ErrorType |
CorrectedBy |
----------------------------------------------------------------------------
-
1 | 1 | 60 | WCO
| U0179C
2 | 0 | 100 | WRO
| U0179C
3 | 1 | 110 | WOP
| U0179C
4 | 0 | 120 | WCNT
| U0179C



Note: "ErrorType + CorrectedBy" is unique. That is a subject will only
correct one error belonging
to each type.

I need to write a query which returns the following table:


Table #3: Results

SubjectID | SessionID | WCO_CORRECT| WRO_CORRECT| WOP_CORRECT|
WCNT_CORRECT|
----------------------------------------------------------------------------
-----------------------------------
U0179C | DF | 1 | 0
| 1 | 0



How do I do this in MS Access ?? Please help... Thanks a lot!
 
Hi Adrian,

I think you want a crosstab similar to:

TRANSFORM First(ErrorCorrectionRecords.AnsCorrect) AS fAnsCorrect
SELECT Subject.SubjectID, Subject.SessionID
FROM Subject INNER JOIN ErrorCorrectionRecords
ON Subject.SubjectID = ErrorCorrectionRecords.CorrectedBy
GROUP BY Subject.SubjectID, Subject.SessionID
PIVOT ErrorCorrectionRecords.ErrorType & "_Correct";
 
Hi,


Basically, a crosstab on an inner join of the first two tables seems to be a
possible solution:

TRANSFORM Last(AnsCorrect)
SELECT table1.SubjectID, SessionID
FROM table1 INNER JOIN table2
ON table1.subjectID=table2.SubjectID
GROUP BY table1.SubjectID, SessionID
PIVOT ErrorType



Hoping it may help,
Vanderghast, Access MVP
 
Hi Adrian,

Just a variation that you might not be
aware of:

I added one value for U0245D to
ErrorCorrectionRecords table:

ID AnsCorrect TimeTaken ErrorType CorrectedBy
1 1 60 WCO U0179C
2 0 100 WRO U0179C
3 1 110 WOP U0179C
4 0 120 WCNT U0179C
5 1 60 WCO U0245D

and when you run this query that manipulates PIVOT

TRANSFORM First(ErrorCorrectionRecords.AnsCorrect) AS fAnsCorrect
SELECT Subject.SubjectID, Subject.SessionID
FROM Subject INNER JOIN ErrorCorrectionRecords
ON Subject.SubjectID = ErrorCorrectionRecords.CorrectedBy
GROUP BY Subject.SubjectID, Subject.SessionID
PIVOT ErrorCorrectionRecords.ErrorType & "_Correct"
IN ('WCO_Correct','WRO_Correct','WOP_Correct','WCNT_Correct');

you would get:

SubjectID SessionID WCO_Correct WRO_Correct WOP_Correct WCNT_Correct
U0179C DF 1 0 1
0
U0245D FS 1

This assumes that you will always have *only* the 4 types.

Good luck,

Gary Walter
 
Back
Top