Full Version: Crosstab Query Needs To Include Null Fields
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
Macros
May 9 2012, 08:18 PM
Hi all,

I got some help last week with a crosstab query and thanks to that I have now been able to create the query that I need.
I am now creating a report based on the query and have encountered a problem. I am posting here in the query forum as I think it is the query that needs fixing and not a report issue.

First of all here is the query

CODE
TRANSFORM Sum(CCur([intFV])) AS Expr1
SELECT Year([CallDate]) AS [Year], Format([CallDate],"ww") AS Week, Calls.CallDate, Sum(CCur([intFV])) AS Vouchers, Sum(Calls.intTV) AS Telstra, Count(Contacts.ContactID) AS [# of Clients], Count(Calls.CallID) AS [# of Interviews], Sum(IIf([MinOfCallDate]=[CallDate],1,0)) AS [New Contacts]
FROM tblPayments INNER JOIN ((Contacts INNER JOIN Qfirst_contact ON Contacts.ContactID = Qfirst_contact.ContactID) INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID) ON tblPayments.ID = Contacts.PaymentType
WHERE (((Year([CallDate]))=2012))
GROUP BY Year([CallDate]), Format([CallDate],"ww"), Calls.CallDate
ORDER BY Calls.CallDate
PIVOT tblPayments.Payment;


The problem I am having is that it only lists headings from the tblPayments table where it is not null.
To better explain (hopefully)
The report works fine when I run it on the entire data set, as there is atleast 1 entry for each of the headers coming from the tblPayments table.
When I alter the query to include only a small date range, then I have a problem.
For example, one of the headers coming from the tblePayments table is "Breached", but if there are no entries for Breached in the date range I select then the heading for Breached is not included in the query results.
This causes a problem as the report has a field for Breached and complains that it isn't there.

I assume I need to alter the query to list all entries from tblPayments and not just those that are used.

As I've written this I've also wondered if it is a report thing and I need to set the report fields to be ignored somehow if the data field doesn't exist.

Thanks in advance

Brendan
tina t
May 9 2012, 08:34 PM
suggest you read up on the ColumnHeadings Property topic in Access Help, which has a bunch of interesting info - i just stumbled across this a couple days ago, and it was all news to me. in part, the topic says

QUOTE
Remarks
You use the ColumnHeadings property to obtain more control over the appearance of column headings in a crosstab query. By default, Microsoft Access displays all data values as column headings in ascending order. For example, if your Column Heading field name is Month, the default column headings displayed will be April, August, December, February, and so on. You can use the ColumnHeadings property to display the data in the correct order with the appropriate setting: "January", "February", "March", and so on.

If you include a column heading in the ColumnHeadings property setting, the column is always displayed in query Datasheet view, even if the column contains no data. This is useful for a report based on a crosstab query, for example, when you always want to display the same column headings in the report.

hth
tina

Macros
May 9 2012, 09:00 PM
Thanks Tina, that looks like exactly what I need, altho' it does seem long winded to have to type every entry from the tblPayments table into the Column Headings property. I'm a little surprised that there isn't a setting to include all entries as column heads.

TFTH

Brendan
Bob G
May 10 2012, 06:36 AM
just a passing comment. I ran into a similar issue where i was creating a quarterly type crosstab and it wouldn't display the quarters that hadn't happened yet. Using the suggestion that Tina provided was the exact fix
tina t
May 10 2012, 06:59 PM
you're welcome, Brendan. sometimes the Access Help, that we all love to hate, does come through for us! ;)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.