Splitting a field based on another field 1

[platypus71]

I have a query that has the following fields:
Name
TestType
Count(tblAnswerKey.QID) AS QsRight


Is it possible to create two new fields, within this query, that are the contents of the 'QsRight' field based on TestType (there are only two test types).

In otherwords, I want pre-QsRight and post-QsRight and if there is no record for one or the other, just leave it blank?

So if the student took only the pre-test, it would show:
Name - pre-QsRight - post-QsRight
John - 10 -
And if they took the post test too, it would show:
John - 10 - 14
And if they only took the post test, it would show:
John - - 14


I know this can be done in another query, but I am hoping to keep it within this one.

 

[PHV]

A starting point (SQL code):
SELECT Name
, Sum(IIf(TestType='pre',QsRight,0)) AS [pre-QsRight]
, Sum(IIf(TestType='post',QsRight,0)) AS [post-QsRight]
FROM yourQuery
GROUP BY Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[platypus71]

When I incorporate this, I got the following error:

Subqueries cannot be used in the expression (IIf([tblPMTest].[TestType]='pre',[QsRight],0)).

 

[PHV]

What are your actual SQL codes ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[platypus71]

SELECT tblPerfManageTest.Name, Count(tblAnswerKey.QID) AS QsRight, Sum(IIf([tblPerfManageTest].[TestType]='pre',[QsRight],0)) AS [pre-QsRight]
FROM qryNumQs INNER JOIN ((tblAnswerKey INNER JOIN qryPerfManageTestConverter ON (tblAnswerKey.QID = qryPerfManageTestConverter.QID) AND (tblAnswerKey.TestName = qryPerfManageTestConverter.TestName)) INNER JOIN tblPerfManageTest ON qryPerfManageTestConverter.[Emp#] = tblPerfManageTest.[Emp#]) ON qryNumQs.TestName = tblAnswerKey.TestName
WHERE (((qryPerfManageTestConverter.GivenAnswer)=[ActualAnswer]))
GROUP BY tblPerfManageTest.Name;

 

[PHV]

Replace this:
Sum(IIf([tblPerfManageTest].[TestType]='pre',[QsRight],0))

with this:
Sum(IIf([tblPerfManageTest].[TestType]='pre',1,0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[platypus71]

Thanks. IIf statements get me every time.

 

[platypus71]

Okay, so this worked great to get the pre-tests scores.
When I added the same code to get the post-tests scores, replacing the word 'pre' with 'post'

post-QsRight: Sum(IIf([tblPerfManageTest].[TestType]='post',1,0))

The math suddenly got wrong. I started having both numbers added together in the QsRight column. And the numbers in the pre-QsRight and post-QsRight were not accurate.

Any ideas?