Crosstab with multiple fields

[elena]

Hi, ALL
I have crosstab which works fine, i need to modify it by adding one more
field:
query now;
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","Valid","Void") IN ("Valid","Void");

result:

badge IssDate SumOfAmount TotalTicks Valid Void
999 3/30/09 $55.00 2 2 0

I need to add one more field (TempTicks.Status) where value is "W" or NULL;

i need result like this:

badge IssDate SumOfAmount TotalTicks Valid Void Warning
999 3/30/09 $55.00 2 2 0 1

Please, help
Should i create another query and them union in crosstab or it is possible
to intergrate it in crosstab?

 

[Duane Hookom]

Try this SQL:

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(Status,"W") = "W")) as Warning
FROM TempTicks
GROUP BY [Shield], [IssueDate]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

 

[elena]

thank you, so much it works great

Try this SQL:

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(Status,"W") = "W")) as Warning
FROM TempTicks
GROUP BY [Shield], [IssueDate]
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");

--
Duane Hookom
Microsoft Access MVP

Hi, ALL
I have crosstab which works fine, i need to modify it by adding one more
field:
query now;
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","Valid","Void") IN ("Valid","Void");

result:

badge IssDate SumOfAmount TotalTicks Valid Void
999 3/30/09 $55.00 2 2 0

I need to add one more field (TempTicks.Status) where value is "W" or NULL;

i need result like this:

badge IssDate SumOfAmount TotalTicks Valid Void Warning
999 3/30/09 $55.00 2 2 0 1

Please, help
Should i create another query and them union in crosstab or it is possible
to intergrate it in crosstab?