Convert blanks to 0 in report

[Matt Williamson]

I have a table with the following entries

Totals salesoffice Type
319655.46 200 Cash Contribution
2607.94 200 Cash Distribution
1078475.47 200 Security Contribution
1369438.83 300 Cash Contribution
43347.03 300 Cash Distribution
1080221.3 300 Security Contribution
47362.84 300 Security Distribution

I'm using a Cross-Tab query as the source for a report. The query is

TRANSFORM Sum([Contributions & Distributions Totals].Totals) AS SumOfTotals
SELECT [Contributions & Distributions Totals].salesoffice
FROM [Contributions & Distributions Totals]
GROUP BY [Contributions & Distributions Totals].salesoffice
PIVOT [Contributions & Distributions Totals].type;

which results in

Salesoffice Cash Distribution Cash Contribution Security Distribution
Security Contribution
200 2607.94 319655.46
1078475.47
300 43347.03 1369438.83 47362.84
1080221.3

When I include a textbox for Security Distribution on the report, it comes
up blank for salesoffice 200 because there isn't an entry in the original
table for it. I'm trying to figure out the best way to get a 0 to display on
the report. Can this be done at the report level or can it be done in the
cross-tab query? If not, what is the best way to handle this? I've tried NZ
but it's not NULL, it just doesn't exist period.

TIA

Matt

 

[John Spencer]

PERHAPS you can use the following modification to your query.

TRANSFORM CDbl(Nz(Sum([Contributions & Distributions Totals].Totals),0)) AS
SumOfTotals
SELECT [Contributions & Distributions Totals].salesoffice
FROM [Contributions & Distributions Totals]
GROUP BY [Contributions & Distributions Totals].salesoffice
PIVOT [Contributions & Distributions Totals].type
IN ("Cash Contribution"
,"Cash Distribution"
,"Security Contribution"
,"Security Distribution")

The transform line will ensure that zero is returned the sum is null.

The In clause will ensure that the four columns are always returned even if
you limit the query to a sales office that does not have one of the four types
of data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have a table with the following entries

Totals salesoffice Type
319655.46 200 Cash Contribution
2607.94 200 Cash Distribution
1078475.47 200 Security Contribution
1369438.83 300 Cash Contribution
43347.03 300 Cash Distribution
1080221.3 300 Security Contribution
47362.84 300 Security Distribution

I'm using a Cross-Tab query as the source for a report. The query is

TRANSFORM Sum([Contributions & Distributions Totals].Totals) AS SumOfTotals
SELECT [Contributions & Distributions Totals].salesoffice
FROM [Contributions & Distributions Totals]
GROUP BY [Contributions & Distributions Totals].salesoffice
PIVOT [Contributions & Distributions Totals].type;

which results in

Salesoffice Cash Distribution Cash Contribution Security Distribution
Security Contribution
200 2607.94 319655.46
1078475.47
300 43347.03 1369438.83 47362.84
1080221.3

When I include a textbox for Security Distribution on the report, it comes
up blank for salesoffice 200 because there isn't an entry in the original
table for it. I'm trying to figure out the best way to get a 0 to display on
the report. Can this be done at the report level or can it be done in the
cross-tab query? If not, what is the best way to handle this? I've tried NZ
but it's not NULL, it just doesn't exist period.

You can use NZ in the query or the text box, if you
guarantee that the entry is included in the query.

This can be done by using an outer join from a table that
has a record for each sales office. Maybe something like:

TRANSFORM Sum([Contributions & Distributions Totals].Totals)
AS SumOfTotals
SELECT [Sales Offices].salesoffice
FROM [Sales Offices] LEFT JOIN [Contributions &
Distributions Totals]
ON [Sales Offices].salesoffice = [Contributions &
Distributions Totals].salesoffice
GROUP BY [Sales Offices].salesoffice
PIVOT [Contributions & Distributions Totals].type

 

[Matt Williamson]

PERHAPS you can use the following modification to your query.

TRANSFORM CDbl(Nz(Sum([Contributions & Distributions Totals].Totals),0))
AS SumOfTotals
SELECT [Contributions & Distributions Totals].salesoffice
FROM [Contributions & Distributions Totals]
GROUP BY [Contributions & Distributions Totals].salesoffice
PIVOT [Contributions & Distributions Totals].type
IN ("Cash Contribution"
,"Cash Distribution"
,"Security Contribution"
,"Security Distribution")

The transform line will ensure that zero is returned the sum is null.

The In clause will ensure that the four columns are always returned even
if you limit the query to a sales office that does not have one of the
four types of data.

That did the trick. Thanks again John!

Matt