crosstab with conditional SUM()

[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
elena

 

[Duane Hookom]

You can create multi-value crosstabs using the solution at
http://www.tek-tips.com/faqs.cfm?fid=4524.