Crosstab Query - Headers

[fenhow]

Hi, I posted earlier about the Partition function. I have been doing some research and looks like I need help with the Crosstab Column Headings.

I read to force column headings in a crosstab query just add them in the "Column Headings" area of the query properties. I do that but when I run the query no data populates in the columns, when I remove it and leave it as default the data comes in.

Am I missing something?

Thanks.
Fen

 

[Ranman256]

The crosstab builds the headings.
But if you want this in a report, I would build a 'report table' with all possible columns.
Append the crosstab to this table and columns needed will be there.

 

[fenhow]

Thank you, that sounds reasonable. However, would I have to append the crosstab table to the 'report table" every time I ran the report? I was trying this on the crosstab but when I added the columns no values would show up..

If the names of the headers are always going to be the same, you can enter them into the query, so those cols will always show regardless of whether they have data or not.

"If you are in design (graphical) view, then open the properties window of the crosstab and you will see a section called Column Headings. Type in the heading names here using quotes and commas such as: "Field1","Field2","Field3", etc..."

 

[stopher]

Yes you can do that. Check out the relevant section here.

 

[fenhow]

Excellent, I have tried this. For some reason when I run the query with the headings added as indicated no data populates. My data is a number with a currency format.

Without the headers added manually the query gives me : 29, 30: 59, 60: 89, and 90:119
I add these in the Column Headings section as the example shows like this:

": 29", "30: 59" etc and it runs, the headings are there but no values?

Suggestions?
Fen

 

[stopher]

You either need to enter your headings as values e.g. 29. Or convert your data values to text.

 

[fenhow]

thank you, entering headings as values? can you give me a bit more detail on that? I am not sure I understand how to do that.
Thanks.
Fen

 

[stopher]

Your example suggests you were entering your headings as text i.e. enclosed with quotes. To enter as values, just omit the quotes. SO if you want headings for values 1, 2 and five then simply enter the following in the column headings property:

1,2,5

 

[fenhow]

Ok I see, how would I do that if the values are a range such as 0 to 29, and 30 to 59?
Thanks.
Fen

 

[stopher]

You would need to create a new field that creates a string to look like your header i.e if the value is say 15 then the text string value in your new field should be "0:29" etc