Crosstab Query Not Functioning With Report

[ahd2008]

Go day all,

I’ve a problem with a report that is based on the crosstab query below.

The purpose of this query is to show the listed below items for all the courses plus to break down the status showing its total individually :

1-Session ID
2-Course Title
3-Course Type
4-Start Date
5-End Date
6-Location
7-Seats
8-Available Seats
9-Status ( In-Progress, Complete, Cancel and No show).

Here is the problem:

When I open up the query itself, it works fine; it shows the following:

(1)-7 (2)-Fire Course (3)-In-House (4)-11/11/2010 (5)-11/15/2010 (6)-London (7)-20 (8)-19 (9)-1 *

•The total of the enrolled employees who have In-Progress status is 1.

However, when I go to the report itself I got an error message saying “the Ms access database engine does not recognize "Cancel" as a valid name or expression.

My query:

TRANSFORM Count(enrollment.[Employee ID]) AS [CountOfEmployee ID]
SELECT Sessions.[Session ID], Courses.[Course Title], Courses.[Course Type], Sessions.[Start Date], Sessions.Location, Sessions.Seats, Sessions.AvailableSeats, Sessions.[End Date]
FROM Courses INNER JOIN (Sessions INNER JOIN enrollment ON Sessions.[Session ID]=enrollment.[Session ID]) ON Courses.[Course ID]=Sessions.[Course ID]
GROUP BY Sessions.[Session ID], Courses.[Course Title], Courses.[Course Type], Sessions.[Start Date], Sessions.Location, Sessions.Seats, Sessions.AvailableSeats, Sessions.[End Date]
PIVOT enrollment.Status;

I’d greatly appreciate any feedback to overcome this problme ?

[TheSmileyCoder]

I haven't seen this issue myself, and have not really used crosstab queries in Access, so im not sure if this will solve your issues, but your problem might be related to the fact that Cancel is a reserved word in Access.

Should be fairly easy to test though, just replace Cancel with Cancelled and see if that works. Also make sure you have no textboxes (or similar controls) in your report named Cancel.

[ahd2008]

Thanks Sir for your feedback; maybe my explanation wasn’t that sufficient let me go more in depth

As per I mentioned, so far the crosstab query only shows this record( the only record there) :

(1)-7 (2)-Fire Course (3)-In-House (4)-11/11/2010 (5)-11/15/2010 (6)-London (7)-20 (8)-19 (9)-1 *

•The total of the enrolled employees who have In-Progress status is 1.

I’ve tried several attempts to overcome this problem by adding new records that has all the possible enrollment status, and it did work. The error message that I got with report as mentioned before says “the Ms access database engine does not recognize "Cancel" as a valid name or expression”; so what I did is that I added new record that has Cancel status. In the case the crosstab query will show:

(1)-7 (2)-Fire Course (3)-In-House (4)-11/11/2010 (5)-11/15/2010 (6)-London (7)-20 (8)-19 (9)-1 * (10)-1**

•The total of the enrolled employees who have In-Progress status is 1.
** the total of the enrolled employees who have Cancel status is 1.

Now, when I open the report I get the same error message but instead of the word Cancel I get Complete. To overcome this problem I entered two more records that have the remaining enrollment status No show & Complete. After all this, it works fine with me now.

However, why this error message pops up when there is no record or when I don’t assign all the possible enrollment status to the records?

From what I can tell, the report should return 0 in case there is no records in the report fields (Cancel, No Show, Complete,In-Progress) or even if not all the entered records has all the possible enrollment status.

I’d appreciate any suggestion to solve this problem.

[Stewart Ross]

Hello. Basing a report on a crosstab query can cause problems as reports need to refer to fixed field names. When you execute a crosstab which results in a pivoted status field like your count of Cancels or No-Shows, the number of field headings returned by the pivot expression reflects the number of different status values in the records aggregated. If your records have only two different status values - Cancel and Completed as you said - then there will be no fields in the resulting crosstab headed with the other values - No Show or In Progress.

In other words, if there are only two of the four possible status values present in your records then two fields expected in your report will be missing from the result of running the report's crosstab recordsource query. The report fails because of these missing fields - which is why you are finding that the report works OK if you enter all possible status values but otherwise errors out.

To resolve this you will need to specify the column headings for the pivot field in your crosstab query. In query design view, right-click on the Column Heading field and select Properties. In the Column Headings property type all possible status values, separated by commas, like this:

"Cancel", "No Show", "Complete", "In-Progress"

If you run the crosstab query after doing so you will see that the number of column headings will reflect the headings you have provided and will not vary if you have no records of one or more of these status values.

Please note two crucial points that arise: firstly, the column headings MUST match the exact spelling of the value pivoted ("In-Progress" is not the same as "In Progress", for example). Second, if you add new status values at a later date you MUST update the manually-set crosstab headings, as records with the new status values will not be aggregated in your crosstab query at all if the manual headings do not match the actual values in your records.

-Stewart

[ahd2008]

Stewart Ross,

You are right;I'm sure that i didn't address my problme properly which led to this confusion. Anyhow, I did what you told me and it is okay now

Thank you for your glorious efforts and TheSmileyOne as well.