How do I force a MS Access crosstab to display a column or row title?

[bobykim]

Hi All,

I'm using MS Access 2003 in a Windows XP environment.

I've created an aging report for my department that is based on what I call the "Main query" with an IIf statement that allows the user to define the dates into 30 day aging segments. Then I've created a crosstab query which counts the results.

How I'd like the report to appear is like this:

Entity_Type  | 1 to 30 days  |   31 to 60 days   |   Over 60 days
OPFACT       |           0           |            0        |              0
PARM        |           10           |           20       |              0
WO             |           30          |           40    |                  0
WR          |               20              |       30      |               0

The problem though is when I run the crosstab query, it will *not* output the "Over 60 day" column in the report because there are no values to count. Furthermore, the output is actually "Null" and not zero. That's another thing I'd like it to force (show a zero vs null). So my reports have outputted like this:

Entity_Type   |  1 to 30 days  |   31 to 60 days    
PARM            |      10           |         20                   
WO               |       30           |        40                    
WR              |         20           |        30

Since OPFACT had no records to count, it does not show up on the report. Similarly, the Over 61 days does not show up either.




Here's my crosstab query:

TRANSFORM Count(EP_Aging_Main_Query.STAT_DATE) AS CountOfSTAT_DATE
SELECT EP_Aging_Main_Query.ENTITY_TYPE, Count(EP_Aging_Main_Query.STAT_DATE) AS [Total Of STAT_Date]
FROM EP_Aging_Main_Query
GROUP BY EP_Aging_Main_Query.ENTITY_TYPE
PIVOT EP_Aging_Main_Query.[Date Interval];

And here is the query that it is based off of:

SELECT [01c_Report_Two_Six].CONCATENATED, [01c_Report_Two_Six].ENTITY_TYPE, [01c_Report_Two_Six].STAT_DATE, IIf([STAT_DATE]<#4/5/2007#,"Over 61 Days",IIf([STAT_DATE] Between #4/4/2007# And #4/8/2007#,"31 to 60 Days",IIf([STAT_DATE] Between #4/7/2007# And #4/14/2007#,"1 to 30 Days"))) AS [Date Interval]
FROM 01c_Report_Two_Six
GROUP BY [01c_Report_Two_Six].CONCATENATED, [01c_Report_Two_Six].ENTITY_TYPE, [01c_Report_Two_Six].STAT_DATE, IIf([STAT_DATE]<#4/5/2007#,"Over 61 Days",IIf([STAT_DATE] Between #4/4/2007# And #4/8/2007#,"31 to 60 Days",IIf([STAT_DATE] Between #4/7/2007# And #4/14/2007#,"1 to 30 Days")));

Any help would be greatly appreciated as I have two other reports that have this same issue.

Thanks!
Bob

[nico5038]

I won't spoil the fun by posting the needed query, but the approach is "simple".
Just define an additional table with the four values needed and use an "OUTER JOIN" (LEFT or RIGHT) to connect this table with the field in your original query that should hold all column values.
Make this table "leading" by clicking on the JOIN-line in the graphical query editor and chosing option 2 (or 3) making this additional table "leading".
Now all values will appear :-)

Nic;o)