Missing Columns in crosstab query

[kmclane]

I am trying to further automate a bunch of reporting. One of the things I am trying to revise is hand pulling data in Excel into different formats and then importing into Access. I have the main data coming in now straight from the .txt file and some trimming and updating of data. Now I am working on a table I used to build in Excel by hand and then import.

I am creating an intermediate table containing an id field and a field that matches certain criteria related to the end table. I then created a table with the seven items containing the code, cost and the label. Now I build the crosstab so I can use it to duplicate the table I was creating by hand.

My problem is that the crosstab drops columns if there is no data and I need the table to contain these fields even if they are empty.

TRANSFORM Sum(POSCost.Cost) AS SumOfCost
SELECT POSTEMP.[Merchant Number]
FROM POSCost INNER JOIN POSTEMP ON POSCost.Material = POSTEMP.Material
GROUP BY POSTEMP.[Merchant Number]
PIVOT POSCost.Label;

I have seven labels defined, 2 of which are never used and one that gets used rarely. Is there a way to force the crosstab to add the columns that don't show without having to add/remove them manually? Sorry for the long winded post. Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dhookom]

You can enter all the possible column headings in the Column Headings property of the crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kmclane]

Thanks, I just figured that out. My brain is getting rusty.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg