Crosstab query needs to return all columns

  • Thread starter Thread starter DSmith
  • Start date Start date
D

DSmith

I need my crosstab query to return all columns even if they are empty. Is
this possible? Help is greatly appreciated.
 
It depends. Do you know the name of the columns that you want returned? If so,
you can specify them in the query grid interface by typing them into the Column
headings property of the query. Separate them with commas and if they are text
surround them with quotes.

Sample SQL statement:
TRANSFORM First(FaqAGAIN.fText) AS FirstOffText
SELECT FaqAGAIN.fSubject
FROM FaqAGAIN
GROUP BY FaqAGAIN.fSubject
PIVOT FaqAGAIN.fID In (1,2,3,4,5);

The "In (1,2,3,4,5)" in the last line will return columns with headings of
1,2,3,4, and 5 (and no others)

If this were text - In ("A","BB","CCC") would return three columns with results
(or not).
 
Thanks, John. It worked great.

John Spencer (MVP) said:
It depends. Do you know the name of the columns that you want returned? If so,
you can specify them in the query grid interface by typing them into the Column
headings property of the query. Separate them with commas and if they are text
surround them with quotes.

Sample SQL statement:
TRANSFORM First(FaqAGAIN.fText) AS FirstOffText
SELECT FaqAGAIN.fSubject
FROM FaqAGAIN
GROUP BY FaqAGAIN.fSubject
PIVOT FaqAGAIN.fID In (1,2,3,4,5);

The "In (1,2,3,4,5)" in the last line will return columns with headings of
1,2,3,4, and 5 (and no others)

If this were text - In ("A","BB","CCC") would return three columns with results
(or not).
Click to expand...
 
Back
Top