Cross Tab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
When I try and save the following crosstab Access closes down. Is there
something wrong with the query or is it a known bug? I'm using Access 2002.
If anyone has a suggestion for an alternative way to produce the data, I
would be very grateful.

TRANSFORM Count([Y6(KS2)Conversion].[Key Stage Year]) AS [CountOfKey Stage
Year]
SELECT [Y6(KS2)Conversion].Grouping,
IIf([Y6(KS2)Conversion]![ReadMatch]=0,Null,CDbl([Y6(KS2)Conversion]![KS1ReadTTPoints])) AS [KS1 Points]
FROM [Y6(KS2)Conversion]
GROUP BY [Y6(KS2)Conversion].Grouping,
IIf([Y6(KS2)Conversion]![ReadMatch]=0,Null,CDbl([Y6(KS2)Conversion]![KS1ReadTTPoints]))
ORDER BY [Y6(KS2)Conversion].Grouping,
IIf([Y6(KS2)Conversion]![ReadMatch]=0,Null,CDbl([Y6(KS2)Conversion]![KS1ReadTTPoints]))
PIVOT IIf([Y6(KS2)Conversion]![ReadMatch]=0,"No
Match",IIf([Y6(KS2)Conversion]![KS2ReadTTPoints]-[Y6(KS2)Conversion]![KS1ReadTTPoints]<0,"<0",IIf([Y6(KS2)Conversion]![KS2ReadTTPoints]-[Y6(KS2)Conversion]![KS1ReadTTPoints]>24,">24",[Y6(KS2)Conversion]![KS2ReadTTPoints]-[Y6(KS2)Conversion]![KS1ReadTTPoints]))) In ("<0",0,2,4,6,8,10,12,14,16,18,20,22,24,">24");


Thanks
Ian
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When this happens to me I usually save the SQL string to a text file;
delete the query; create a new query & paste the saved SQL string into
the query's SQL view & save the query. Then I compact the db & try the
query again. Compacting the db gets rid of the old query version, which
"hangs around" after deletion. If the old version was, somehow,
corrupted this procedure gets rid of the corruption.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQe+NHYechKqOuFEgEQI07wCgyJVgl+p4KZ9BqaysxS2aFamsrQwAn3Yc
rJ+ArAHvomH09d9R5tZO5yg9
=vTq/
-----END PGP SIGNATURE-----
 
Thanks. It's nice to know that I'm not the only one who has these sorts of
problems!

Ian

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When this happens to me I usually save the SQL string to a text file;
delete the query; create a new query & paste the saved SQL string into
the query's SQL view & save the query. Then I compact the db & try the
query again. Compacting the db gets rid of the old query version, which
"hangs around" after deletion. If the old version was, somehow,
corrupted this procedure gets rid of the corruption.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQe+NHYechKqOuFEgEQI07wCgyJVgl+p4KZ9BqaysxS2aFamsrQwAn3Yc
rJ+ArAHvomH09d9R5tZO5yg9
=vTq/
-----END PGP SIGNATURE-----


Ian said:
Hi,
When I try and save the following crosstab Access closes down. Is there
something wrong with the query or is it a known bug? I'm using Access 2002.
If anyone has a suggestion for an alternative way to produce the data, I
would be very grateful.

TRANSFORM Count([Y6(KS2)Conversion].[Key Stage Year]) AS [CountOfKey Stage
Year]
SELECT [Y6(KS2)Conversion].Grouping,
IIf([Y6(KS2)Conversion]![ReadMatch]=0,Null,CDbl([Y6(KS2)Conversion]![KS1ReadTTPoints])) AS [KS1 Points]
FROM [Y6(KS2)Conversion]
GROUP BY [Y6(KS2)Conversion].Grouping,
IIf([Y6(KS2)Conversion]![ReadMatch]=0,Null,CDbl([Y6(KS2)Conversion]![KS1ReadTTPoints]))
ORDER BY [Y6(KS2)Conversion].Grouping,
IIf([Y6(KS2)Conversion]![ReadMatch]=0,Null,CDbl([Y6(KS2)Conversion]![KS1ReadTTPoints]))
PIVOT IIf([Y6(KS2)Conversion]![ReadMatch]=0,"No
Match",IIf([Y6(KS2)Conversion]![KS2ReadTTPoints]-[Y6(KS2)Conversion]![KS1ReadTTPoints]<0,"<0",IIf([Y6(KS2)Conversion]![KS2ReadTTPoints]-[Y6(KS2)Conversion]![KS1ReadTTPoints]>24,">24",[Y6(KS2)Conversion]![KS2ReadTTPoints]-[Y6(KS2)Conversion]![KS1ReadTTPoints]))) In ("<0",0,2,4,6,8,10,12,14,16,18,20,22,24,">24");
Click to expand...
Click to expand...
 
Back
Top