Crosstab query help

G

Guest

Hi, All
I need to modify current query to return following result for the report.
Summary Report should look like:
Badge Unit IssueDate OTNotices Regular Total
600 24 07/18/07 0 57 57
603 03 07/18/07 14 8 22

Is it possible?
query:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Unit, IssueDate
FROM TempTicks
GROUP BY [Shield], Unit, IssueDate
PIVOT IIf([ViolCode]="151" Or [ViolCode]="141","OTNotices","Regular") In
("OTNotices","Regular");

Please, advice
 
J

John Spencer

Line 5 below should give you the total count
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge
, Unit
, IssueDate
, Count(TempTicks.TicketNo) as TotalTicks
FROM TempTicks
GROUP BY [Shield], Unit, IssueDate
PIVOT IIf([ViolCode]="151" Or [ViolCode]="141","OTNotices","Regular") In
("OTNotices","Regular");

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

Thank you, it looks great

John Spencer said:
Line 5 below should give you the total count
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge
, Unit
, IssueDate
, Count(TempTicks.TicketNo) as TotalTicks
FROM TempTicks
GROUP BY [Shield], Unit, IssueDate
PIVOT IIf([ViolCode]="151" Or [ViolCode]="141","OTNotices","Regular") In
("OTNotices","Regular");

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

elena said:
Hi, All
I need to modify current query to return following result for the report.
Summary Report should look like:
Badge Unit IssueDate OTNotices Regular Total
600 24 07/18/07 0 57 57
603 03 07/18/07 14 8 22

Is it possible?
query:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, Unit, IssueDate
FROM TempTicks
GROUP BY [Shield], Unit, IssueDate
PIVOT IIf([ViolCode]="151" Or [ViolCode]="141","OTNotices","Regular") In
("OTNotices","Regular");

Please, advice
Click to expand...
Click to expand...
 

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

Similar Threads

Crosstab with multiple fields 2
Crosstab query for the report 2
Crosstab query with multiple fields 2
crosstab with conditional SUM() 1
Crosstab query 3
How to remove the prompt criteria from the report ? 4
How to use ASC in PIVOT ? 2
Crosstab query, help 5

Top