Legends

[SamMexico via AccessMonster.com]

Hi everyone, this might seem like the most daft question ever but I'd
appreciate any input...

I have a pie chart that is based on a query that counts the check boxes in
the table. My problem is that the pie chart gives a binary 1 or 0 for yes or
no in the pie chart legend and after editing it always reverts back to 1 and
0...

I presume I have to edit the query somehow but at the moment I'm at a loss...

TIA

Sam

 

[golfinray]

Checkboxes will give you a -1 for yes and a 0 for no. Convert that in your
query to whatever you need. IIF([yourcheckboxfield]=-1,"Yes","No")

 

[John W. Vinson]

Hi everyone, this might seem like the most daft question ever but I'd
appreciate any input...

I have a pie chart that is based on a query that counts the check boxes in
the table. My problem is that the pie chart gives a binary 1 or 0 for yes or
no in the pie chart legend and after editing it always reverts back to 1 and
0...

I presume I have to edit the query somehow but at the moment I'm at a loss...

Well, you're ahead of us: at any rate you can *see* the query and know what it
does. We can't tell even that!

Please open the query in SQL view and post it here so someone might be able to
help.

 

[SamMexico via AccessMonster.com]

Hi chaps,

Here is the SQL for the query...

SELECT Data.Region AS Region, Count(Data.Prophylaxis) AS CountOfProphylaxis,
Data.Prophylaxis
FROM Data
GROUP BY Data.Region, Data.Prophylaxis
HAVING (((Data.Region)="Leicester") AND ((Data.Prophylaxis) Is Null)) OR ((
(Data.Region)="Leicester") AND ((Data.Prophylaxis) Is Not Null));

 

[John W. Vinson]

Hi chaps,

Here is the SQL for the query...

SELECT Data.Region AS Region, Count(Data.Prophylaxis) AS CountOfProphylaxis,
Data.Prophylaxis
FROM Data
GROUP BY Data.Region, Data.Prophylaxis
HAVING (((Data.Region)="Leicester") AND ((Data.Prophylaxis) Is Null)) OR ((
(Data.Region)="Leicester") AND ((Data.Prophylaxis) Is Not Null));

This query makes no sense. It's selecting all records from Leicester in an
inefficient way - finding all for which Prophylaxis is NULL and then all for
which it is NOT NULL, and combining those. Why ask about Prophylaxis in the
first place if you're going to retrieve all records anyway?

For that matter, a Yes/No field can never be NULL so it makes even less sense!

And you say you're "editing" the chart. To what? What are you editing, and
what do you want to see? If you open this query in datasheet view I'm guessing
you'll see something like

Leicester; 31; -1
Leicester; 24; 0

assuming that Prophylaxis is a Yes/No field.

If you want to see the words "Yes" and "No" you could use

SELECT Data.Region AS Region, Count(*), IIF([Data].[Prophylaxis], "Yes", "No")
AS Proph
FROM Data
GROUP BY Data.Region, IIF([Data].[Prophylaxis], "Yes", "No")
WHERE Data.Region = "Leicester";

This moves the criterion from HAVING (applied after all the totalling is done)
to WHERE (done first), removes the redundant critera, and recasts the -1 and 0
into text. You can of course use other words than "yes" or "no" in the
expression.

 

[SamMexico via AccessMonster.com]

Thanks John, that works a treat!

Sam