Formatting Chart for Yes and No instead of -1 and 0

[efandango]

I have a comparison chart which measures a column of 'Yes' and 'No's'. When I
run the chart, the Legend shows '0' and '-1' instead of plain text Yes and
No. The underlying data is in real 'Yes' and 'No' format and not "text"
format. I have tried to adjust it in the charts DataSource query grid, but
their is no 'Format' option for that column. I have also treid adjusting it
in the 'datasheet' box on the chart, it accepts the new defintions, but
doesn't save them. Is there a way of changing the names in the SQL text.

My SQL for the chart is:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT Tbl_Scores_Running_Totals.answer_box;

The Yes and No's relate to the [answer_box] field.

 

[Duane Hookom]

Try change your Row Source property to:
TRANSFORM Count(*) AS [Count]
SELECT IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No") as RunNo
FROM Tbl_Scores_Running_Totals
GROUP BY IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No")
PIVOT Tbl_Scores_Running_Totals.answer_box;

 

[efandango]

HI Duanne,

I pasted your SQL in, and I now get the word "Yes", between the two columns
in the chart. But the Legend still says 0 and -1, instead of Yes and No.

My data in the query looks like this: (correct data, but incorrect names for
columns)

RunNo -1 0
Yes 52 38

Try change your Row Source property to:
TRANSFORM Count(*) AS [Count]
SELECT IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No") as RunNo
FROM Tbl_Scores_Running_Totals
GROUP BY IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No")
PIVOT Tbl_Scores_Running_Totals.answer_box;


--
Duane Hookom
Microsoft Access MVP

I have a comparison chart which measures a column of 'Yes' and 'No's'. When I
run the chart, the Legend shows '0' and '-1' instead of plain text Yes and
No. The underlying data is in real 'Yes' and 'No' format and not "text"
format. I have tried to adjust it in the charts DataSource query grid, but
their is no 'Format' option for that column. I have also treid adjusting it
in the 'datasheet' box on the chart, it accepts the new defintions, but
doesn't save them. Is there a way of changing the names in the SQL text.

My SQL for the chart is:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT Tbl_Scores_Running_Totals.answer_box;

The Yes and No's relate to the [answer_box] field.

 

[Duane Hookom]

My bad, try:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT IIf(answer_box=-1,"Yes", "No") IN ("Yes", "No");

--
Duane Hookom
Microsoft Access MVP

HI Duanne,

I pasted your SQL in, and I now get the word "Yes", between the two columns
in the chart. But the Legend still says 0 and -1, instead of Yes and No.

My data in the query looks like this: (correct data, but incorrect names for
columns)

RunNo -1 0
Yes 52 38

Try change your Row Source property to:
TRANSFORM Count(*) AS [Count]
SELECT IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No") as RunNo
FROM Tbl_Scores_Running_Totals
GROUP BY IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No")
PIVOT Tbl_Scores_Running_Totals.answer_box;


--
Duane Hookom
Microsoft Access MVP

I have a comparison chart which measures a column of 'Yes' and 'No's'. When I
run the chart, the Legend shows '0' and '-1' instead of plain text Yes and
No. The underlying data is in real 'Yes' and 'No' format and not "text"
format. I have tried to adjust it in the charts DataSource query grid, but
their is no 'Format' option for that column. I have also treid adjusting it
in the 'datasheet' box on the chart, it accepts the new defintions, but
doesn't save them. Is there a way of changing the names in the SQL text.

My SQL for the chart is:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT Tbl_Scores_Running_Totals.answer_box;

The Yes and No's relate to the [answer_box] field.

 

[efandango]

Oh Mercy!

that's the one...

thanks Duanne.

My bad, try:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT IIf(answer_box=-1,"Yes", "No") IN ("Yes", "No");

--
Duane Hookom
Microsoft Access MVP

HI Duanne,

I pasted your SQL in, and I now get the word "Yes", between the two columns
in the chart. But the Legend still says 0 and -1, instead of Yes and No.

My data in the query looks like this: (correct data, but incorrect names for
columns)

RunNo -1 0
Yes 52 38

Try change your Row Source property to:
TRANSFORM Count(*) AS [Count]
SELECT IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No") as RunNo
FROM Tbl_Scores_Running_Totals
GROUP BY IIf(Tbl_Scores_Running_Totals.Run_No,"Yes","No")
PIVOT Tbl_Scores_Running_Totals.answer_box;


--
Duane Hookom
Microsoft Access MVP


:

I have a comparison chart which measures a column of 'Yes' and 'No's'. When I
run the chart, the Legend shows '0' and '-1' instead of plain text Yes and
No. The underlying data is in real 'Yes' and 'No' format and not "text"
format. I have tried to adjust it in the charts DataSource query grid, but
their is no 'Format' option for that column. I have also treid adjusting it
in the 'datasheet' box on the chart, it accepts the new defintions, but
doesn't save them. Is there a way of changing the names in the SQL text.

My SQL for the chart is:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT Tbl_Scores_Running_Totals.answer_box;

The Yes and No's relate to the [answer_box] field.

 

[Helen Martin]

ok.. I have a similar problem, and found this discussion but can't quite
figure out how to apply to to my variant..

I'm making a pie chart.. also with a series of yes and no answers...

my application is a survey.. we asked a question? do you find the new
location convenient? and got a series of yes and no answers..

my sql looks like this..

SELECT qselconvenient.[new location convenient?], Count(*) AS [Count]
FROM qselconvenient
GROUP BY qselconvenient.[new location convenient?];

I don't think I need a pivot because I only have one series.. and I
think I should be able to kind of use this IIF code, but
I don't really understand how to apply this IIF to my query...

can someone point me in the correct direction...

with thanks
Helen In Canada

Oh Mercy!

that's the one...

thanks Duanne.

My bad, try:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT IIf(answer_box=-1,"Yes", "No") IN ("Yes", "No");

 

[Duane Hookom]

Try a row source for your chart of:
SELECT IIf(qselconvenient.[new location convenient?]=-1,"Yes","No") As
ConvenientLocation, Count(*) AS [Count]
FROM qselconvenient
GROUP BY IIf(qselconvenient.[new location convenient?]=-1,"Yes","No");

Your survey sounds quite un-normalized if you have fields with names of
questions. Have you found the At Your Survey demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane?

--
Duane Hookom
Microsoft Access MVP

ok.. I have a similar problem, and found this discussion but can't quite
figure out how to apply to to my variant..

I'm making a pie chart.. also with a series of yes and no answers...

my application is a survey.. we asked a question? do you find the new
location convenient? and got a series of yes and no answers..

my sql looks like this..

SELECT qselconvenient.[new location convenient?], Count(*) AS [Count]
FROM qselconvenient
GROUP BY qselconvenient.[new location convenient?];

I don't think I need a pivot because I only have one series.. and I
think I should be able to kind of use this IIF code, but
I don't really understand how to apply this IIF to my query...

can someone point me in the correct direction...

with thanks
Helen In Canada

Oh Mercy!

that's the one...

thanks Duanne.

My bad, try:

TRANSFORM Count(*) AS [Count]
SELECT Tbl_Scores_Running_Totals.Run_No
FROM Tbl_Scores_Running_Totals
GROUP BY Tbl_Scores_Running_Totals.Run_No
PIVOT IIf(answer_box=-1,"Yes", "No") IN ("Yes", "No");

 

[Helen Martin]

Try a row source for your chart of:
SELECT IIf(qselconvenient.[new location convenient?]=-1,"Yes","No") As
ConvenientLocation, Count(*) AS [Count]
FROM qselconvenient
GROUP BY IIf(qselconvenient.[new location convenient?]=-1,"Yes","No");

Your survey sounds quite un-normalized if you have fields with names of
questions. Have you found the At Your Survey demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane?

thank you .. your suggestion was helpful and reminded me of how to use
IIF...

I am curious about your comment though...

I am familiar with the idea of normalization and concentrate on building
most of my databases that way... I do it anytime I see that storing
the data requires a structure that is essentially three dimensional..

however, I'm not sure about your assumption that this would be required
for any survey.. someone did refer me to a survey app a couple of
months ago, which I did download and look at but it was much more
complex that I needed...

in my case, I have a short survey, (not of MY design) with 9 questions,
about half of which asked for yes/no answers and the other half offered
a opportunity for a write in comment..

I actually don't see an situations which requires normalization there..

I'd like to learn from this.. can you give me one or two real short
examples of the kinds of things in a survey which you build a new table
for??

with appreciation
Helen

 

[Duane Hookom]

My comments would be based on a basic understanding of the At Your Survey
demo. Did you check it out to see how a normalized survey app can be
structured? If so, do you have some specific questions?
--
Duane Hookom
Microsoft Access MVP

Try a row source for your chart of:
SELECT IIf(qselconvenient.[new location convenient?]=-1,"Yes","No") As
ConvenientLocation, Count(*) AS [Count]
FROM qselconvenient
GROUP BY IIf(qselconvenient.[new location convenient?]=-1,"Yes","No");

Your survey sounds quite un-normalized if you have fields with names of
questions. Have you found the At Your Survey demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane?

thank you .. your suggestion was helpful and reminded me of how to use
IIF...

I am curious about your comment though...

I am familiar with the idea of normalization and concentrate on building
most of my databases that way... I do it anytime I see that storing
the data requires a structure that is essentially three dimensional..

however, I'm not sure about your assumption that this would be required
for any survey.. someone did refer me to a survey app a couple of
months ago, which I did download and look at but it was much more
complex that I needed...

in my case, I have a short survey, (not of MY design) with 9 questions,
about half of which asked for yes/no answers and the other half offered
a opportunity for a write in comment..

I actually don't see an situations which requires normalization there..

I'd like to learn from this.. can you give me one or two real short
examples of the kinds of things in a survey which you build a new table
for??

with appreciation
Helen