I need the Column Heading in Croostab Query always show all column even if it does not have data (1 Viewer)

[Sokkheng]

In Crosstab query i need to show in column heading by product name mean i have 7 product name and i want it always show all 7 product name however in some product name don't have data i need it show that column name also (In Crosstab Query for Column heading it show only the product name that have data, product name that don't have data in that period it not show) when i run the query.
Thanks.

 

[ebs17]

Two possibilities:

1) (static) Fixed column headings

2) (dynamic) tblProducts LEFT JOIN tblData

 

[Pat Hartman]

#2, which is the left join to your product table, is the most flexible. If you use method #1, you will need to remember to change the query if you add a new product.

 

[CJ_London]

To do fixed column headings, look for the column headings in the query properties and enter the products you want to see, eg

prodname1, prodname3, prodname4, etc

 

[Kamayo Ako]

In Crosstab query i need to show in column heading by product name mean i have 7 product name and i want it always show all 7 product name however in some product name don't have data i need it show that column name also (In Crosstab Query for Column heading it show only the product name that have data, product name that don't have data in that period it not show) when i run the query.
Thanks.

Maybe this will help

 

[Pat Hartman]

Not sure why the others are pushing the column headers solution. "product" is an entity and will almost certainly expand over time causing you maintenance work. The fixed column headers is more appropriate to something that is either fixed like months in a year or which is very unlikely to change such as marital status.

 

[ebs17]

the others

Some are open to what is to come.

For a clever query solution, one would need to know the data schema, which is planned and undisputed.

Needless to say, a basic good approach is to align your programming so that a little extra data doesn't require reprogramming. To put it more bluntly, if some additional data (which one might expect) requires reprogramming, then one has made critical mistakes.

 

[Pat Hartman]

Thank you for mansplaining what I said

 

[shoji]

Open the crosstab query in SQL. At the end of the statement, add the IN clause with all the headers to show like

... PIVOT ... IN ("A", "B", "C", ...);

This way, regardless of whether there are any data for columns or not all the headers will appear.

Shoji