Crosstab query

  • Thread starter Thread starter Renate
  • Start date Start date
R

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
 
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');
 
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

Duane said:
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


Renate said:
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
Click to expand...
Click to expand...
 
Works for me.

Renate said:
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

Duane said:
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


Renate said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top