Crosstab query

[Renate]

Hi all,

I have a table which lists contraventions. The table has among other
fields, one keyfield which contains the id of the contravention
(ReportNo) and one with the date the contravention is made
(DateContravention).
I need to create a query that shows the total number of contraventions
per month in a given year, so I tried to create a crosstab query to do
that.

The SQL I've got is this:

TRANSFORM Count(NameTable.ReportNo) AS NumberofReportNo
SELECT Year([DateContravention]) AS [Year contravention]
FROM SF5620
GROUP BY Year([DateContravention])
PIVOT Nz(Month([DateContravention]),0) In
('1','2','3','4','5','6','7','8','9','10','11','12');

However, in the result of the query the columns with no reports are
empty. Somehow the empty value won't work with the NZ function.
Anybody any clue on why this happens and how I can change that?

TIA,
Renate

 

[Duane Hookom]

Try
TRANSFORM Val(Nz(Count(NameTable.ReportNo),0)) AS NumberofReportNo
SELECT Year([DateContravention]) AS [Year contravention]
FROM SF5620
GROUP BY Year([DateContravention])
PIVOT Nz(Month([DateContravention]),0) In
('1','2','3','4','5','6','7','8','9','10','11','12');

 

[Renate]

Hi Duane,

Thanks for your reply! I've tried that but it didn't work as well. I
found the solution however, I hadn't selected the option for expression
in the Count field. Now all works fine.

Thanks again!

Kind regards,
Astrid

Try
TRANSFORM Val(Nz(Count(NameTable.ReportNo),0)) AS NumberofReportNo
SELECT Year([DateContravention]) AS [Year contravention]
FROM SF5620
GROUP BY Year([DateContravention])
PIVOT Nz(Month([DateContravention]),0) In
('1','2','3','4','5','6','7','8','9','10','11','12');

--
Duane Hookom
MS Access MVP

Hi all,

I have a table which lists contraventions. The table has among other
fields, one keyfield which contains the id of the contravention
(ReportNo) and one with the date the contravention is made
(DateContravention).
I need to create a query that shows the total number of contraventions
per month in a given year, so I tried to create a crosstab query to do
that.

The SQL I've got is this:

TRANSFORM Count(NameTable.ReportNo) AS NumberofReportNo
SELECT Year([DateContravention]) AS [Year contravention]
FROM SF5620
GROUP BY Year([DateContravention])
PIVOT Nz(Month([DateContravention]),0) In
('1','2','3','4','5','6','7','8','9','10','11','12');

However, in the result of the query the columns with no reports are
empty. Somehow the empty value won't work with the NZ function.
Anybody any clue on why this happens and how I can change that?

TIA,
Renate

 

[Guest]

Works for me.

Hi Duane,

Thanks for your reply! I've tried that but it didn't work as well. I
found the solution however, I hadn't selected the option for expression
in the Count field. Now all works fine.

Thanks again!

Kind regards,
Astrid

Try
TRANSFORM Val(Nz(Count(NameTable.ReportNo),0)) AS NumberofReportNo
SELECT Year([DateContravention]) AS [Year contravention]
FROM SF5620
GROUP BY Year([DateContravention])
PIVOT Nz(Month([DateContravention]),0) In
('1','2','3','4','5','6','7','8','9','10','11','12');

--
Duane Hookom
MS Access MVP

Hi all,

I have a table which lists contraventions. The table has among other
fields, one keyfield which contains the id of the contravention
(ReportNo) and one with the date the contravention is made
(DateContravention).
I need to create a query that shows the total number of contraventions
per month in a given year, so I tried to create a crosstab query to do
that.

The SQL I've got is this:

TRANSFORM Count(NameTable.ReportNo) AS NumberofReportNo
SELECT Year([DateContravention]) AS [Year contravention]
FROM SF5620
GROUP BY Year([DateContravention])
PIVOT Nz(Month([DateContravention]),0) In
('1','2','3','4','5','6','7','8','9','10','11','12');

However, in the result of the query the columns with no reports are
empty. Somehow the empty value won't work with the NZ function.
Anybody any clue on why this happens and how I can change that?

TIA,
Renate