Crosstab query with multiple fields

  • Thread starter Thread starter elena
  • Start date Start date
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 the result like this:
Badge SumOfAmount TotalTicks Void Valid
999 $150.00 3 0 1
222 $70.00 2 1 1

How can i modify the query to calculate total amount on "Void" and "Valid"
separatly :
Badge SumOfAmount TotalTicks Void VoidAmt Valid ValidAmt
999 $150.00 3 0 $00.00 1
$150.00
222 $70.00 2 1 $20.00 1
$50.00

Please, help
 
You might use a different query that is not a crosstab query at all.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Shield] AS Badge
, Sum(Master.AmtVl) AS SumOfAmount
, Sum(IIF(DisposCode="VD",AmtV1,Null)) as VoidAmt
, Sum(IIF(DisposCode="VD",Null,AmtV1)) as ValidAmt
, Abs(Sum(DisposCode="VD")) as VoidCount
, Abs(Sum(DisposCode<>"VD")) as ValidCount
, Nz(COUNT(Master.TickNo),0) AS TotalTicks
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you, John, it works great

John Spencer said:
You might use a different query that is not a crosstab query at all.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Shield] AS Badge
, Sum(Master.AmtVl) AS SumOfAmount
, Sum(IIF(DisposCode="VD",AmtV1,Null)) as VoidAmt
, Sum(IIF(DisposCode="VD",Null,AmtV1)) as ValidAmt
, Abs(Sum(DisposCode="VD")) as VoidCount
, Abs(Sum(DisposCode<>"VD")) as ValidCount
, Nz(COUNT(Master.TickNo),0) AS TotalTicks
FROM Master
WHERE Master.IssDate Between [Enter Start Date] AND [Enter End Date]
GROUP BY [Shield]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
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 the result like this:
Badge SumOfAmount TotalTicks Void Valid
999 $150.00 3 0 1
222 $70.00 2 1 1

How can i modify the query to calculate total amount on "Void" and "Valid"
separatly :
Badge SumOfAmount TotalTicks Void VoidAmt Valid ValidAmt
999 $150.00 3 0 $00.00 1
$150.00
222 $70.00 2 1 $20.00 1
$50.00

Please, help
Click to expand...
.
Click to expand...
 
Back
Top