Crosstable column for empty number field

[Batox]

I'm starting out with a wizard-created crosstable and a wizard-created report. The crosstable looks like this:

SQL Query:
TRANSFORM Count(qBase.Code) AS numberOfCode
SELECT qBase.question 
FROM qBase 
GROUP BY qBase.question 
ORDER BY qBase.question 
PIVOT qBase.answer;

Result:
question   <>   1   2   3   4   5
q1         5    12  55  9   4   1
q2         1    4   9   15  21  32
...

It contains for each question the information how many times each answer (1-5) was given. The "<>" column tells me how many times no answer at all was given (that is how many times qBase.answer had no value). It was added automatically by the wizard, I have no idea how.

Since there are various filter criteria, I don't want to use the wizard-generated static crosstable query, but instead use a dynamically generated SQL statement, which I assign to the report in the report_open handler with me.RecordSource = mySQLstring.

I have already learned that I must explicitly include the column headings to the PIVOT statement. When I simply add (1,2,3,4,5), all is fine, except that I don't get the "<>" column. I alread tried (1,2,3,4,5, "<>"), but that doesn't work of course... ?

 

[Privateer]

Batox:
Open the query in design mode and right click on the upper half field where the tables go. Select properties and if in 2007, click on the field again. You want to find the properties option. Once in there, locate column headings and type in whatever you want. Perhaps, "Zero","One","Two",Three","Four","Five". This will allow you to give any name to the resultant fields.

I will only say that I don't like putting crosstab queries as the source to a report, but that is another question.
Privateer

 

[Batox]

Batox:
I will only say that I don't like putting crosstab queries as the source to a report, but that is another question.
Privateer

Thanks for your reply, and sorry for the long delay - just too much to do

I know how to rename columns in a static query. My question is how do to that at runtime in SQL. But your "other question" could lead to a much better solution. How would you design the report I need without using a crosstab query as source?

My basic query provides data in the following form:

name   question   answer
John   1          3
John   2          
Anna   1          3
Anna   2          1
Lisa   1          4
Lisa   2          1
Joe    1          2
Joe    2          5

I need a report which looks like this:

Question   Ans1   Ans2   An3   Ans4   Ans5   <>
1          1      0      2     0      1      0
2          1      1      0     1      0      1

 

[stopher]

Just create a new field with a formula like this:

ColField: IIF(IsNUll([answer],"<>","Ans" & [answer])

You can then use that field as the column heading instead of answer. Note that the "<>" doesn't need to be "<>". You can make it "blank" or "incomplete".

hth
Chris

 

[Batox]

Just create a new field with a formula like this:

ColField: IIF(IsNUll([answer],"<>","Ans" & [answer])

You can then use that field as the column heading instead of answer.

OK, the above formula creates an extra column in my basic query. Now I need to use the values of this column as column headers in the report. You write I can use them "instead of answer", but somehow I'm at a loss here...

 

[stopher]

I've attached an example.

 

[Batox]

I've attached an example.

Well, the example contains a crosstable query with the needed layout. Privateer wrote that he doesn't like to use pivot queries as data source for reports. My question is how I could create a report with this crosstable-alike layout right from the basic table, without an intermediate pivot query.

 

[vbaInet]

Well, the example contains a crosstable query with the needed layout. Privateer wrote that he doesn't like to use pivot queries as data source for reports. My question is how I could create a report with this crosstable-alike layout right from the basic table, without an intermediate pivot query.

Can't be done unless you are good at coding. It's not even a feasible solution.

 

[stopher]

I agree with vbaInet. I don't see any problems with using pivot queries for the purpose you require.

Where pivot tables can get tricky is where the column headings are variable e.g. Attendee might be different from one conference to the next so it's tricky creating a report with fixed headings.