Hi John,
Sorry for the mis-understanding - I didn't realize that you were just using
the Pivot Chart view of your query. It's not something that I ever do, so
I'm not particularly familiar with it. However, having looked at it again,
here's a possible way around your problem.
The Pivot Chart view almost certainly constructs the equivalent of a
crosstab query internally, based on the fields you drag into the various
areas. So you need to feed it a with a table or select (including totals)
query - the sort of thing that you would base a crosstab query on. You
can't feed it a crosstab query. Alternatively, you can create a regular
chart based on a crosstab query, which will give the same finished item as
the pivot chart (and that's what I assumed you were doing). But let's
forget that for the moment, and stick with getting things to work using a
pivot chart view of your data.
The problem is that you do not have all the possible answers in your
existing query. So you need to get them into the query. The way to do that
is to set up a separate table containing all possible answers (I'll call it
tblAllAnswers here, with a field [Answer]), join that to your existing table
with an outer join (right or left, depending on how you construct it - in
the query design grid, after joining the answer fields, right-click on the
join line and select "Show all fields from tblAllAnswers and only
corresponding fields from [tblQ_3_g to i]), and then use the Answers field
from the AllPossibleAnswersTable in your GroupBy clause. The SQL for your
new query will be:
SELECT tblAllAnswers.Answer, Count([tblQ_3_g to i].[3hi How Likely?]) AS
[Count]
FROM tblAllAnswers LEFT JOIN [[tblQ_3_g to i] ON tblAllAnswers.Answer =
[tblQ_3_g to i].[3hi How Likely?]
GROUP BY tblAllAnswers.Answer;
You can change the name of the Answer field, or alias it with an AS clause,
if you want it to appear with different wording on your pivot chart.
HTH,
Rob
JohnW said:
Really struggling with creating a pivot chart from a cross tab. How do
you
get the 1 to 10 categories to appear along the bottom with the count total
as
the y axis? I must be missing something obvious.
--
John Whyte
JohnW said:
Thanks, Rob.
I can see how to do it using a crosstab.
Actually what I had done was just to switch to PivotChart view from a
simple
two colum query. The SQL is as follows.
SELECT [tblQ_3_g to i].[3hi How Likely?] AS [Likely out of 10],
Count([tblQ_3_g to i].[3hi How Likely?]) AS [Count]
FROM [tblQ_3_g to i]
GROUP BY [tblQ_3_g to i].[3hi How Likely?];
so I did not set it up as a cross tab.
Unless there is a way to create the missing categories on a simple switch
in
view from my original SQL I will have to try to recreate it as a Pivot
Chart
based on a new crosstab query into the data.
Many thanks,
John
--
John Whyte
:
Hi John,
You haven't detailed how you've set up your pivot chart. But I assume
that
it's based on a crosstab query. If so, what you need to do is to force
all
the possible answer fields into the columns in that query. The way to
do
that is to enter them into the Column Headings field in the property
sheet
of the query (as a comma-separated list). If you view the SQL of the
resulting query you will find that the PIVOT clause is followed by an
IN
clause containing all the possible answers; something like:
...
PIVOT tblName.Answers IN (1, 2, 3, ...)
If the answers field is a text field rather than a number, you'll need
quote
delimiters, eg:
...
PIVOT tblName.Answers IN ("1", "2", "3", ...)
HTH,
Rob
Thanks for your input but that's not quite the problem. I perhaps
have
not
been clear enough.
I have a query with (so far) thirty records, one for each respondent.
The
answer field shows one number in the range 1 to 10. They can only
select
one
number in the range 1 to 10. None of the respondents selected 6 or 9
so
none
of the records has a 6 or 9 in the answer field.
The Pivot chart view shows a bar chart with the count of the answers
on
the
y axis. For example, 14 people said 1 and 3 people said 2, etc. No
one
said 6
or 9 so these two are missing from the x axis. The x axis is
supposed to
have the categories as the range 1 to 10, but, because the is no
record
with
a 6 or a 9 then there are no categories for 6 and 9. There is no
space on
the x axis showing 6 or 9. In other words, the count value of
category 6
and
category 9 is zero.
I would like to have the zero count categories showing (as you
normally
would in a bar chart). So the x axis would read from 1 to 10 even if
6
and 9
are zero/missing.
Hope that clarifies my problem.
--
John Whyte
:
I will assume that your questionaire table has the 10 answers set to
be
data
type Yes/No.
I suspect that you are getting hit by NULLs, which aren't the same a
NO.
Let me assume your table is named QTable, and the definition looks
like
this:
question (text)
ans_01 (yes/no)
ans_02 (yes/no)
...
ans_10(yes/no)
First thing I'd do is go into the table definition and make sure
that the
Ans_xx columns have a default value of 0.
Then I'd run the following query
UPDATE QTable SET ans_01 TO 0 WHERE ( ans_01 Is Null )
repeat for each answer. (Be sure to change both references to the
columns!)
Then I'd go back to the table definition and set "Required" to YES.
Now run your cross tabs, it should work as it no longer discards
NULL
values.
