Null values in a cross tab query

  • Thread starter shannaj via AccessMonster.com
  • Start date

S

shannaj via AccessMonster.com

I am trying to set my null values to zero with the nz function in my query
for monthly sales totals, but I can not figure how to use it. Here is my SQL,
can someone please help?

TRANSFORM Sum(qryCustomerSales2006.Amount) AS SumOfAmount
SELECT qryCustomerSales2006.[Customer ID], Sum(qryCustomerSales2006.Amount)
AS [Total Of Amount]
FROM qryCustomerSales2006 LEFT JOIN tblCustomerList ON qryCustomerSales2006.
[Customer ID] = tblCustomerList.[Customer ID]
GROUP BY qryCustomerSales2006.[Customer ID]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");
 
Ad

Advertisements

A

Allen Browne

Try changing the first line to:
TRANSFORM Nz(Sum(qryCustomerSales2006.Amount),0) AS SumOfAmount

To get Access to recognise the column as Currency, you probably want:
TRANSFORM CCur(Nz(Sum(qryCustomerSales2006.Amount),0)) AS SumOfAmount

More info:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html
 
Ad

Advertisements

S

shannaj via AccessMonster.com

That worked! Thank you very much!
I am trying to set my null values to zero with the nz function in my query
for monthly sales totals, but I can not figure how to use it. Here is my SQL,
can someone please help?

TRANSFORM Sum(qryCustomerSales2006.Amount) AS SumOfAmount
SELECT qryCustomerSales2006.[Customer ID], Sum(qryCustomerSales2006.Amount)
AS [Total Of Amount]
FROM qryCustomerSales2006 LEFT JOIN tblCustomerList ON qryCustomerSales2006.
[Customer ID] = tblCustomerList.[Customer ID]
GROUP BY qryCustomerSales2006.[Customer ID]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");
 

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