in the PIVOT field, make sure to include IN clause and supply values in your
column headings.

See SQL below.

good luck.

TRANSFORM nz(Sum([CommonSkill]))+0 AS [Value]
SELECT tmpPMCommon.PROJ_ID, Sum(tmpPMCommon.CommonSkill) AS TotalBB, "J0" AS
Sort, "Common Skills" AS [Desc], "45" AS lb
FROM tmpPMCommon
WHERE (((tmpPMCommon.TSD_CD) In ("YY","CN","BB")))
GROUP BY tmpPMCommon.PROJ_ID
PIVOT tmpPMCommon.IBB_DT In ( 09/01/1998, 09/02/1998, 09/03/1998,
09/04/1998, 09/05/1998, 09/06/1998, 09/07/1998, 09/08/1998, 09/09/1998,
09/10/1998, 09/11/1998, 09/12/1998, 09/13/1998, 09/14/1998);

I have been trying to generate a chart on a report. The chart is based on a
CrossTab query which is easy until we use a Crosstab which has a different
number of fields from the original.

Data is a series of surveys of a number of years on a number of variables.
Wanting to chart the results over a number of years. All variables have the
same number of values 1-7.

Example:

First Run - Crosstab creates a table with fields labelled 1,2,3,4,5,6 and 7.
Great - graph works well.

Second run - Crosstab retrieves data but only has fields 3,4,5,6 and 7.
AAGH.

How do I get the data to work over all runs so that I always get fields 1-7.
I have thought about looking at the resulting table, check which fields
exist and add new blank ones. But the code eludes me at present. I think
it's called brain fade.

TIA

Andrew

Thanks - will try.

Andrew
----------