Help with Crosstab Query 1

[bernie10]

Hello everyone,
I’m new to working with crosstab queries so perhaps someone can offer some help. Suppose I have data as follows:

Company TicketNumber NextStep
Company 1 1 Action1
Company 1 2 Action2
Company 1 3 Action3
Company 2 4 Action2
Company 2 5 Action1
Company 1 6 Action1

So my crosstab query for the above data is:

Company Action1 Action2 Action3
Company 1 2 1 1
Company 2 1 1 0


I then use this data from the crosstab query in other queries. My concern is that it is quite possible that one of the steps may not appear in the original table for a particular period (the data is updated regularly). For example next period there may not be any company with “Action3”, leading this field to disappear from my crosstab query. Then my other queries which use this field will throw up an error saying they can’t find this field. Is there any way to make the field appear in the query even if there aren’t any records- so if ticket number 3 from the above table got deleted the crosstab query would look like:

Company Action1 Action2 Action3
Company 1 2 1 0
Company 2 1 1 0

The “Action 3” field is still there (so my other queries can still use it) even though the values for the field are all zero. I will never have more than these three actions so I would like to have the crosstab query always show these fields whether there is data for them or not.

Does anyone know how I can accomplish this?

Many thanks,
Collen

 

[lupins46]

Look at the query properties and put your fixed column headings in the Column Headings property:
"Action1","Action2","Action3"
but you must spell the headings exactly as they appear in your data.

 

[bernie10]

Perfect, thanks.

Collen