Query Error Based on Crosstab Query

[Andy]

I have a select query that runs off a crosstab query. Within the cross tab
query, the column heading can generate either two or three columns (depending
on the data content), say A,B &C.

There are occassions when all 3 of these columns will be present and
sometimes when just A&B are present. When C is missing, my select query falls
over as it cannot find C to report on. Can I re-write in anyway to let the
select query report either way?

This is my select query SQL (C relates to the Unknown_CS field): SELECT
[q_Clock Stop Counts by Specialty].[Specialty Code], [q_Clock Stop Counts by
Specialty].Spec_Desc, [q_Clock Stop Counts by Specialty].Unknown_CS AS Expr1,
[q_Clock Stop Counts by Specialty].[<=18 Weeks], [q_Clock Stop Counts by
Specialty].[>18 Weeks], IIf([q_Clock Stop Counts by Specialty]![>18 Weeks] Is
Null,1,IIf([q_Clock Stop Counts by Specialty]![<=18 Weeks] Is Null,0,[q_Clock
Stop Counts by Specialty]![<=18 Weeks]/([q_Clock Stop Counts by
Specialty]![<=18 Weeks]+[q_Clock Stop Counts by Specialty]![>18 Weeks]))) AS
[Compliancy (%age)]
FROM [q_Clock Stop Counts by Specialty]
GROUP BY [q_Clock Stop Counts by Specialty].[Specialty Code], [q_Clock Stop
Counts by Specialty].Spec_Desc, [q_Clock Stop Counts by
Specialty].Unknown_CS, [q_Clock Stop Counts by Specialty].[<=18 Weeks],
[q_Clock Stop Counts by Specialty].[>18 Weeks], IIf([q_Clock Stop Counts by
Specialty]![>18 Weeks] Is Null,1,IIf([q_Clock Stop Counts by Specialty]![<=18
Weeks] Is Null,0,[q_Clock Stop Counts by Specialty]![<=18 Weeks]/([q_Clock
Stop Counts by Specialty]![<=18 Weeks]+[q_Clock Stop Counts by
Specialty]![>18 Weeks])));

 

[John Spencer]

You need to rewrite the Crosstab query. Using A, B, and C as column
names your Pivot clause would read like

PIVOT [SomeField] in ("A","B","C")

Or more probable
PIVOT [SomeField] in (""Unknown","<=18 Weeks",">18 Weeks")

If you don't know how to do this is SQL view, then select the query
properties in query design view and type them into the column headings
property. You must type them exactly as the crosstab will only return
columns for the exact values you enter into the column headings, but it
will return a column for each value you enter into the column headings.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Rob Parker]

Hi Andy,

I suggest that you change your crosstab query that the select query is based
on to force it to include all columns. In the SQL for the query, this will
require an IN clause following the PIVOT clause, such as:
...
PIVOT fieldname IN ("A", "B", "C")

You can do this in the query design grid, by entering the desired column
names in the Column Headings field of the query's property dialog box (as a
comma-separated list).

HTH,

Rob

 

[Andy]

Cheers John, worked great.

You need to rewrite the Crosstab query. Using A, B, and C as column
names your Pivot clause would read like

PIVOT [SomeField] in ("A","B","C")

Or more probable
PIVOT [SomeField] in (""Unknown","<=18 Weeks",">18 Weeks")

If you don't know how to do this is SQL view, then select the query
properties in query design view and type them into the column headings
property. You must type them exactly as the crosstab will only return
columns for the exact values you enter into the column headings, but it
will return a column for each value you enter into the column headings.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a select query that runs off a crosstab query. Within the cross tab
query, the column heading can generate either two or three columns (depending
on the data content), say A,B &C.

There are occassions when all 3 of these columns will be present and
sometimes when just A&B are present. When C is missing, my select query falls
over as it cannot find C to report on. Can I re-write in anyway to let the
select query report either way?

This is my select query SQL (C relates to the Unknown_CS field): SELECT
[q_Clock Stop Counts by Specialty].[Specialty Code], [q_Clock Stop Counts by
Specialty].Spec_Desc, [q_Clock Stop Counts by Specialty].Unknown_CS AS Expr1,
[q_Clock Stop Counts by Specialty].[<=18 Weeks], [q_Clock Stop Counts by
Specialty].[>18 Weeks], IIf([q_Clock Stop Counts by Specialty]![>18 Weeks] Is
Null,1,IIf([q_Clock Stop Counts by Specialty]![<=18 Weeks] Is Null,0,[q_Clock
Stop Counts by Specialty]![<=18 Weeks]/([q_Clock Stop Counts by
Specialty]![<=18 Weeks]+[q_Clock Stop Counts by Specialty]![>18 Weeks]))) AS
[Compliancy (%age)]
FROM [q_Clock Stop Counts by Specialty]
GROUP BY [q_Clock Stop Counts by Specialty].[Specialty Code], [q_Clock Stop
Counts by Specialty].Spec_Desc, [q_Clock Stop Counts by
Specialty].Unknown_CS, [q_Clock Stop Counts by Specialty].[<=18 Weeks],
[q_Clock Stop Counts by Specialty].[>18 Weeks], IIf([q_Clock Stop Counts by
Specialty]![>18 Weeks] Is Null,1,IIf([q_Clock Stop Counts by Specialty]![<=18
Weeks] Is Null,0,[q_Clock Stop Counts by Specialty]![<=18 Weeks]/([q_Clock
Stop Counts by Specialty]![<=18 Weeks]+[q_Clock Stop Counts by
Specialty]![>18 Weeks])));