crosstab converts Zeros to Nulls workaround?

  • Thread starter Thread starter Tom Green
  • Start date Start date
T

Tom Green

Access 2000/2003

Jet appears to be converting Zeros to Nulls in a Crosstab, however only when
the row has non-zero entries to sum. I go out of my way to populate a temp
table with a grid of zeros so my reports are 'pretty', then Jet breaks the
neat rows of zeros y filling in only values with a non-zero entry as below.
Any workaround for this?

Descriptor1 Total Of Value 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
19 20 21 22 23 24
Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 6







6















Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 3







3















Descriptor1 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 2







2















Descriptor1 4








4














Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 3







3















Descriptor1 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 13







13















Descriptor1 6








6














Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 4







4















Descriptor1 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 1







1 1














Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 5







5















Descriptor1 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 2







2 2














Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 20







20















Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 5







5















Descriptor1 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 5







5















Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Descriptor1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
 
Sorry, pasted table of sample data is totally jumbled in posting, does not
give visual example.
 
I suspect the problem is the Inner Join, which is absolutely necessary:

TRANSFORM Sum([RPT_MVARHExchange].[Value]) AS SumOfValue
SELECT [DailyOps_PointIDMapping].[ReportLabel],
[DailyOps_PointIDMapping].[FriendlyName], [RPT_MVARHExchange].[Date],
[RPT_MVARHExchange].[ReportGroup], [RPT_MVARHExchange].[Descriptor1],
[RPT_MVARHExchange].[Value] AS [Total Of Value]
FROM RPT_MVARHExchange INNER JOIN DailyOps_PointIDMapping ON
[RPT_MVARHExchange].[PointID]=[DailyOps_PointIDMapping].[PointID]
GROUP BY [DailyOps_PointIDMapping].[ReportLabel],
[DailyOps_PointIDMapping].[FriendlyName], [RPT_MVARHExchange].[Date],
[RPT_MVARHExchange].[ReportGroup], [RPT_MVARHExchange].[Descriptor1],
[RPT_MVARHExchange].[Value]
ORDER BY [DailyOps_PointIDMapping].[ReportLabel] DESC ,
[DailyOps_PointIDMapping].[FriendlyName]
PIVOT [RPT_MVARHExchange].[HrEnd];
 
You can try use:
TRANSFORM Val(Nz(Sum([RPT_MVARHExchange].[Value]),0)) AS SumOfValue
 
Works flawlessly, thank you very much. I had never encountered the Val
function, pasting in the help note on this for anyone searching the archive:

Val Function
Returns the numbers contained in a string as a numeric value of appropriate
type.

Syntax

Val(string)

The required string argument is any valid string expression.

Remarks

The Val function stops reading the string at the first character it can't
recognize as part of a number. Symbols and characters that are often
considered parts of numeric values, such as dollar signs and commas, are not
recognized. However, the function recognizes the radix prefixes &O (for
octal) and &H (for hexadecimal). Blanks, tabs, and linefeed characters are
stripped from the argument.

The following returns the value 1615198:

Val(" 1615 198th Street N.E.")
In the code below, Val returns the decimal value -1 for the hexadecimal
value shown:

Val("&HFFFF")
Note The Val function recognizes only the period (.) as a valid decimal
separator. When different decimal separators are used, as in international
applications, use CDbl instead to convert a string to a number.


Duane Hookom said:
You can try use:
TRANSFORM Val(Nz(Sum([RPT_MVARHExchange].[Value]),0)) AS SumOfValue

--
Duane Hookom
MS Access MVP


Tom Green said:
I suspect the problem is the Inner Join, which is absolutely necessary:

TRANSFORM Sum([RPT_MVARHExchange].[Value]) AS SumOfValue
SELECT [DailyOps_PointIDMapping].[ReportLabel],
[DailyOps_PointIDMapping].[FriendlyName], [RPT_MVARHExchange].[Date],
[RPT_MVARHExchange].[ReportGroup], [RPT_MVARHExchange].[Descriptor1],
[RPT_MVARHExchange].[Value] AS [Total Of Value]
FROM RPT_MVARHExchange INNER JOIN DailyOps_PointIDMapping ON
[RPT_MVARHExchange].[PointID]=[DailyOps_PointIDMapping].[PointID]
GROUP BY [DailyOps_PointIDMapping].[ReportLabel],
[DailyOps_PointIDMapping].[FriendlyName], [RPT_MVARHExchange].[Date],
[RPT_MVARHExchange].[ReportGroup], [RPT_MVARHExchange].[Descriptor1],
[RPT_MVARHExchange].[Value]
ORDER BY [DailyOps_PointIDMapping].[ReportLabel] DESC ,
[DailyOps_PointIDMapping].[FriendlyName]
PIVOT [RPT_MVARHExchange].[HrEnd];
Click to expand...
Click to expand...
 
Back
Top