Crosstab query for the report

[elena]

Hi, All
I am using Access 2003
I need to modify crosstab query;
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, [IssueDate] AS IssDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks,
Sum(Abs(Nz(TempTicks.Status,"W")="W")) AS Warning
FROM TempTicks
GROUP BY [Shield], [IssueDate]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

In the TempTick table i have fields:
ActCode where the value could be "10" which mean "Valid" and other values
like from "92" up to "95" which means "Void"
Besides that table has field "Status" where only 2 different value could be
like
"W" - stands for "Warning" or value= " ".
scenario ; ActCode="10" and Status=" " - it means "Valid"
ActCode="10" and Status="W" - it means "warning"
ActCode="92" and Status= " " - means "Void"

my query produce not accurate result, example:
Valid Void Warning TotalTickets Amount
1 0 1 1 $20.00

but in reality it should be like this:
Valid Void Warning TotalTickets Amount
0 0 1 1 $20.00
because Valid ticket can't be warning;

Please, help, How can i change the query to produce accurate output?
thank you,
Is it possible at all?

 

[Daryl S]

Elena -

Change the Pivot line of your code to include the test for Warning as follows:

PIVOT IIf([ActCode]="10", IIf
Nz(TempTicks.Status,"W")="W"),"Warning","Valid"),"Void") IN
("Valid","Void","Warning");

 

[elena]

Thank you, Daryl! it works, now the query looks like this:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, [IssueDate] AS IssDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks
GROUP BY [Shield], [IssueDate]
PIVOT
IIf([ActCode]="10",IIf(Nz(TempTicks.Status="W"),"Warning","Valid"),"Void") In
("Warning","Valid","Void");

Elena -

Change the Pivot line of your code to include the test for Warning as follows:

PIVOT IIf([ActCode]="10", IIf
Nz(TempTicks.Status,"W")="W"),"Warning","Valid"),"Void") IN
("Valid","Void","Warning");

--
Daryl S

Hi, All
I am using Access 2003
I need to modify crosstab query;
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, [IssueDate] AS IssDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks,
Sum(Abs(Nz(TempTicks.Status,"W")="W")) AS Warning
FROM TempTicks
GROUP BY [Shield], [IssueDate]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

In the TempTick table i have fields:
ActCode where the value could be "10" which mean "Valid" and other values
like from "92" up to "95" which means "Void"
Besides that table has field "Status" where only 2 different value could be
like
"W" - stands for "Warning" or value= " ".
scenario ; ActCode="10" and Status=" " - it means "Valid"
ActCode="10" and Status="W" - it means "warning"
ActCode="92" and Status= " " - means "Void"

my query produce not accurate result, example:
Valid Void Warning TotalTickets Amount
1 0 1 1 $20.00

but in reality it should be like this:
Valid Void Warning TotalTickets Amount
0 0 1 1 $20.00
because Valid ticket can't be warning;

Please, help, How can i change the query to produce accurate output?
thank you,
Is it possible at all?