Crosstab query Help with Totals

E

elena

Hi, All
I have a query, all credits to (Chris2)

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountOfTick
SELECT TempTicks.Shield
FROM TempTicks
GROUP BY TempTicks.Shield
PIVOT fWeekdayName(DatePart("w",TempTicks.IssueDate)) & " " &
TempTicks.IssueDate;

Result:
Sheld Sunday 09/17/2007 Monday 09/11/2007 .... Total
999 35 45 80
?
111 60 15 75
?
_______________________________________________
Total 95 ? 60 ? 155 ?

How can i set "Control source" property for the Totals in the singal record
and for the column?
Please, help
 
K

KARL DEWEY

Try this ---
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountOfTick
SELECT TempTicks.Shield, Nz(COUNT(TempTicks.TicketNo),0) AS Total
FROM TempTicks
GROUP BY TempTicks.Shield
PIVOT fWeekdayName(DatePart("w",TempTicks.IssueDate)) & " " &
TempTicks.IssueDate;

Use report footer for column totals.
 
E

elena

Thank you, Karl
It works great.

KARL DEWEY said:
Try this ---
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountOfTick
SELECT TempTicks.Shield, Nz(COUNT(TempTicks.TicketNo),0) AS Total
FROM TempTicks
GROUP BY TempTicks.Shield
PIVOT fWeekdayName(DatePart("w",TempTicks.IssueDate)) & " " &
TempTicks.IssueDate;

Use report footer for column totals.
 

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

Top