By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,280 Members | 1,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,280 IT Pros & Developers. It's quick & easy.

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

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. Entity_Type  | 1 to 30 days  |   31 to 60 days   |   Over 60 days
  2. OPFACT       |           0           |            0        |              0
  3. PARM        |           10           |           20       |              0
  4. WO             |           30          |           40    |                  0
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. Entity_Type   |  1 to 30 days  |   31 to 60 days    
  2. PARM            |      10           |         20                   
  3. WO               |       30           |        40                    
  4. 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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(EP_Aging_Main_Query.STAT_DATE) AS CountOfSTAT_DATE
  2. SELECT EP_Aging_Main_Query.ENTITY_TYPE, Count(EP_Aging_Main_Query.STAT_DATE) AS [Total Of STAT_Date]
  3. FROM EP_Aging_Main_Query
  4. GROUP BY EP_Aging_Main_Query.ENTITY_TYPE
  5. PIVOT EP_Aging_Main_Query.[Date Interval];

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

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM 01c_Report_Two_Six
  3. 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.

Jun 10 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 3,072
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 :-)

Jun 10 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.