Crosstab Query Problem

[Guest]

Hi all
I have a crosstab (B) query calling another crosstab (A). The problem occurs on crosstab A in which 2 columns are missing. Then I realise that those 2 columns has no data that is why they are not there. Is there anyway to work around this

any help would be much appreciated

TIA
Djoezz

 

[Allen Browne]

Include the names of all the potential columns in the Column Headings
property of the crosstab query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,
I have a crosstab (B) query calling another crosstab (A). The problem

occurs on crosstab A in which 2 columns are missing. Then I realise that
those 2 columns has no data that is why they are not there. Is there anyway
to work around this?

 

[Guest]

Hi Allen
Thanks for the respond but how do I do that
I have 3 possible column headers which are "Pass", "Reject", and "Transfd

Below is my SQL
TRANSFORM nz(Count([tblInsertTransaction].[fldInsertID]),0) AS Expr
SELECT tblInsert.fldInsertDesign, tblInsert.fldType, tblInsert.fldPowe
FROM tblInsert INNER JOIN tblInsertTransaction ON tblInsert.fldInsertID = tblInsertTransaction.fldInsertI
WHERE (((tblInsertTransaction.fldDate)<=#5/10/2004#) AND ((Year([tblInsertTransaction].[fldDate]))=2004) AND ((tblInsertTransaction.fldTransactionType) In ('Pass','Reject','Transfd'))
GROUP BY tblInsert.fldInsertDesign, tblInsert.fldType, tblInsert.fldPowe
ORDER BY tblInsert.fldInsertDesign, tblInsert.fldTyp
PIVOT tblInsertTransaction.fldTransactionType

thanks
Djoezz

 

[Allen Browne]

1. Open your crosstab query in design view.

2. Open the Properties box (view menu).

3. Enter the 3 items, separated by semicolons, beside the Column Headings
property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
Thanks for the respond but how do I do that?
I have 3 possible column headers which are "Pass", "Reject", and "Transfd"

Below is my SQL:
TRANSFORM nz(Count([tblInsertTransaction].[fldInsertID]),0) AS Expr1
SELECT tblInsert.fldInsertDesign, tblInsert.fldType, tblInsert.fldPower
FROM tblInsert INNER JOIN tblInsertTransaction ON tblInsert.fldInsertID = tblInsertTransaction.fldInsertID
WHERE (((tblInsertTransaction.fldDate)<=#5/10/2004#) AND

((Year([tblInsertTransaction].[fldDate]))=2004) AND
((tblInsertTransaction.fldTransactionType) In ('Pass','Reject','Transfd')))

 

[John Spencer (MVP)]

You can specify which columns will appear.

Open Crosstab A and in its properties, specify the column names in the Column
Headings property. The query will then return ONLY the columns you specify and
If there is nothing for the column the column will still be returned.

 

[Guest]

Thank you Allen, and you too John! :-

Cheers
Djoezz