Crosstab Query And Report Help

[Reese]

[Solved] Crosstab Query And Report Help

Here's my goal:

1) In an unbound report have multiple sub reports

2) Each subreport is bound to a select query which in turn is based on a crosstab. (The reason for this is to force all categories in the crosstab's Row Heading to appear, even if there is no value for that category, based on the content of a dummy table holding the full list of possible Row Heading categories.)

3) The crosstabs contain criteria parameters that reference Start Date and End Date text fields in a form (this way the user enters the start and end date parameters once without having multiple parameter prompts appear).

I have created all of the queries and when the final queries are run individually (as queries not via reports) they work perfectly.


Here are my road blocks:

1) I tried to create the sub reports in the unbound report using the sub report wizard. However when selecting the desired query none of the fields are shown in the Available Fields box. (This is true for all of the queries I'm trying to use.)

2) I then manually created a continuous report related to one of the desired queries from scratch. When run by itself as a report this report works perfectly.

3) I then used the sub report wizard to insert that newly created report into the unbound report. This worked fine in the main report's Design View.

4) When I try to see the main, unbound report in Report View, I get a warning that "You Can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport."

5) I tried using the initial crosstab query (prior using the select query to insert all of the row heading values) in the SubReport Wizard to create a subreport from that query. However, again, none of the fields appear in the Available Fields box.

How can I get around this? I have read that using temporary tables may work, but I've never used them before and am confused by what I've read. Plus I want to avoid the whole bloat problem since the end result--once it's complete--will be used in a split database.

Any help would be appreciated. Thanks.

 

[94Sport5sp]

Hi:

Which version of access?

I believe your problem lies with how a crosstab query returns results. When working with crosstab queries it is important to remember that the field names are the content of the values returned. This means than when you create a report or form based on a crosstab query your field names will vary. Also, when an item has no data to be represented in a crosstab then that field name does not show up.

So, assuming you are doing a crosstab on employee last name arrange by date then the employee names become your field names. This means that the field names will look something like Date, Smith, Jones, West, etc for all the employees found. If the next time you run the query and there is no data for Jones then the query will return Date, Smith, West, etc for all the employees found. (Jones is not in the list and is now not a valid field name).

Trust that helps.

 

[Reese]

I'm using Access 2007.

when an item has no data to be represented in a crosstab then that field name does not show up

I know, that's why I sent the crosstabs through select queries with a join link to a dummy table that included all of the fields I want.

I want this because it's a report that will display information for 7 different types of programs that will remain constant. Here's what I mean:

Program Type_______Programs
ZooMobile _________#
Guided Tour ________#
Birthday Party ______ #
Scout Program ______#
Wild Encounter _____ #
Special Event _______#
Zoo School ________#

There are 4 crosstabs, one for Number of Participants, one for Number of Programs, one for Number of Clients and one for Income. Each one displays the totals for each category from the Start Date to the End Date.

Because this is a formal report which will be given to many people--including the zoo's Board of Directors--I want the categories to be constant, even if there's no programs in that category between the Start Date and End Date.

I have already used these same techniques an Annual Report, which displays the exact same thing. I used slightly different and field values as the columns, however, as those queries were set up to display the information in the following way:

Program Type_____ January____February... etc.
ZooMobile _______#_________#
Guided Tour ______#_________#
etc.

How come it worked with the annual report and not the Start and End Date version?

 

[94Sport5sp]

Hi:

Can you share what the last query (the one the report is based on) looks like?

Also are you familiar with the Column Headings property of the Query Properties?

 

[Reese]

Here's the last query:

SELECT DummyTable.Row_Heading_Full, [Detailed Participants Crosstab Query].Total AS Participants
FROM DummyTable LEFT JOIN [Detailed Participants Crosstab Query] ON DummyTable.Row_Headings=[Detailed Participants Crosstab Query].Program_Code
GROUP BY DummyTable.Row_Heading_Full, [Detailed Participants Crosstab Query].Total;

Here's the crosstab that it's based on (called Detailed Participants Crosstab Query):

PARAMETERS [Forms]![Reports Page]![StartDateTxt] DateTime, [Forms]![Reports Page]![EndDateTxt] DateTime;
TRANSFORM Sum([Number of Participants Calculation].[Number of Participants]) AS [SumOfNumber of Participants]
SELECT [Number of Participants Calculation].Program_Code
FROM [Number of Participants Calculation]
WHERE ((([Number of Participants Calculation].Date_of_Event) Between [Forms]![Reports Page]![StartDateTxt] And [Forms]![Reports Page]![EndDateTxt]))
GROUP BY [Number of Participants Calculation].Program_Code
PIVOT [Number of Participants Calculation].Ed_Update_Complete;

Here's the Number of Participants Calculation query that the crosstab is based on:

SELECT [Event Information].Date_of_Event, Sum(nz([Event Information].Number_of_Participants)+nz([Event Information].Actual_Youth_or_Scouts)+nz([Event Information].[Actual_Adults-Other])) AS [Number of Participants], ("Total") AS Ed_Update_Complete, [Event Information].Program_Code
FROM [Event Information]
WHERE ((([Event Information].Ed_Update_Complete)="-1")) OR ((([Event Information].Tour_Num_Complete)=True))
GROUP BY [Event Information].Date_of_Event, [Event Information].Program_Code, [Event Information].Number_of_Participants, [Event Information].Actual_Youth_or_Scouts, [Event Information].[Actual_Adults-Other], [Event Information].Ed_Update_Complete, [Event Information].Tour_Num_Complete;

Also are you familiar with the Column Headings property of the Query Properties?

No, I'm not. What does it do?

 

[Reese]

Oops, forgot to clarify, the above is for number of participants. The problem is the same across all four sets of queries: income, number of participants, number of programs and number of clients.

 

[94Sport5sp]

Hi:

Column Headings provides as simplistic means of establishing column headings for crosstab queries and ensure a fixed number of columns are returned even when no data is present.

Check out http://msdn.microsoft.com/en-us/library/office/aa191550(v=office.11).aspx for some ideas.

I will be away for several days. If no one else comments I will get back to you then

 

[94Sport5sp]

Hi:

Creating reports (and forms even more so) based on cross tab queries can be challenging especially when the field names and field types can change. Your idea of using the dummy table to control the possible field names is good but needs some way of connecting it back to the cross tab query.

I think what you want is described in support.microsoft.com/kb/328320/en-us which is a more detailed discussion on creating dynamic reports that are based on parameter cross tab queries.

Trust that helps

 

[Reese]

I actually found a solution by going back to the annual report version of the queries. These one's aren't based on a flexible start & end date in a form, instead the date is set to January 1st to December 31st of the current year. Like so:

TRANSFORM NZ(Count([Event Information].Event_ID),0) AS [Number of Programs]
SELECT [Event Information].Program_Code, nz(Count([Event Information].Event_ID),0) AS Annual
FROM [Event Information]
WHERE (((Year([Date_of_Event]))=Year(Date())) AND (([Event Information].Date_of_Event)<=Date()))
GROUP BY [Event Information].Program_Code
PIVOT Format([Date_of_Event],"mmmm") In (January,February,March,April,May,June,July,August,September,October,November,December);

I used the same code, but changed the WHERE statement to the following:

WHERE ((([Event Information].Date_of_Event) Between [Forms]![Reports Page]![StartDateTxt] And [Forms]![Reports Page]![EndDateTxt]))

I passed it through the dummy table to force all of the program types to appear. Then, in order to remove the months and only show the total, I only used the Annual total when creating the report.

So to summarize, when using Date_of_Event as a column heading everything works fine. As soon as I tried using a different field as the column heading and only use the Date_of_Event as a hidden criteria everything got messed up. The dummy table wasn't the issue at all.

I don't know why Access was giving me a problem, nor do I know why my solution works but it does.

Thanks anyway!

 

[94Sport5sp]

Hi:

Glad that you got it to work. Thanks for the feed back.

The cross tab feature of access is quite useful but can be a pain to understand. Something is different between the two fields and that is where the problem lies. The dummy table is not part of the problem but does show up the problem.