crosstab query with Sum -conditional

E

elena

Hi, All
my query:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Nz(COUNT(Master.TickNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Sum(Master.AmtVl) AS SumOfAmount,
Nz(COUNT(Master.TickNo),0) AS TotalTicks
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]
PIVOT IIf([DisposCode]="VD","Void","Valid") IN ("Void","Valid");
produce result like

Badge SumOfAmount TotalTicks Void Valid
999 $150.00 3 1 2

How can i modify query with two more conditional Sum()s to get the result
like this:
Badge SumOfAmount TotalTicks Void VoidAmt Valid ValidAmt
999 $150.00 3 1 $20.00 2
$130.00

Please, help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

crosstab with conditional SUM() 1
Crosstab query with multiple fields 2
Crosstab with multiple fields 2
Crosstab query for the report 2
How to remove the prompt criteria from the report ? 4
Inner Join doesn't work, Help 0
CrossTab query w/param 5
CrossTab quiry with Parameters 4

Top