473,703 Members | 2,898 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

1 New Member
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
1 4106
3,080 Recognized Expert Specialist
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

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

Similar topics

by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I want them to do as little as possible when they run their reports. I have a crosstab query that displays usage of items for each month. It looks pretty much like this: ITEM DESC UM 12/02 1/03 2/03 3/03 ...ETC. 1 Solution ...
by: Richard Hollenbeck | last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank you. I try to ask relevant questions so that they don't just benefit me, but also benefit the group. I'm currently overwhelmed by useless examples across the web on how to make "dynamic crosstab reports" without myself having a basic understanding about how to retrieve and assign recordsources, etc., from fields in a query to fields in the report. I see all these...
by: Big Time | last post by:
I've got a crosstab query that counts the number of values in one of my tables. However, rather than giving me the number of instances of each record, what I would like is for each value to display, separated by a comma. For example, in the query is a row is named BC and a column named Vancouver, it currently displays "4" for the number of instances that Vancouver appears combined with the BC field. However, what I would like to display...
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic sub-report will capture what grades the student has achieved in a list of different subjects and the reason I need it to be dynamic is that students take different subjects. Basically I've been trying to doctor the KB article on dynamic
by: Brad | last post by:
Thanks for taking the time to read my question. I have a table of data that has Date, Data and Category. I need to show, in a report, each Categories Data by Date. The Date has to be it's own column across the top and Category down the left side. As data is entered, the number of unique dates increases. As a result the
by: Nenad Markovic | last post by:
Hi everybody, When executing a Crosstab Query I see only rows (defined in a row heading) that have values (defined in value field) in at least one column (defined as column headings). How can I make a Crosstab Query that shows all rows, regardless they have values in column headings. For instance, the result that I get now is like this
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually launching Excel for data viewing. I'd prefer the user stay in Access. Creating dynamic crosstab queries is pretty simple. The problem is that the column count may shrink or grow depending on the filter.
by: helm | last post by:
Folks, could anyone advise ... Is there a significant difference in crosstab capabilities in Access and Excel? Using Office XP 2002 ... to produce a crosstab report I developed it in Excel from a query datasource in Access. One good thing about this was the ability to select both a date header and then a specific time header for the crosstab value. Try as I might, I cannot see a way to replicate this in Access either directly using a...
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which has the following fields: SPID (supervisor ID), total:group by, as row heading Date, total:group by, as column heading Calls handled, total:sum, as value Date, total:where, criteria between and - this is taken from a form,
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.