Crosstab Query Problem

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

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
 
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.

Djoezz said:
Hi all,
I have a crosstab (B) query calling another crosstab (A). The problem
Click to expand...
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?
 
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
 
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.

Djoezz said:
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
Click to expand...
((Year([tblInsertTransaction].[fldDate]))=2004) AND
((tblInsertTransaction.fldTransactionType) In ('Pass','Reject','Transfd')))
 
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.
 
Back
Top