From: SamMexico via AccessMonster.com on
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1

From: golfinray on
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")
--
Milton Purdy
ACCESS
State of Arkansas


"SamMexico via AccessMonster.com" wrote:

> 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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1
>
> .
>
From: John W. Vinson on
On Mon, 26 Apr 2010 09:54:55 GMT, "SamMexico via AccessMonster.com"
<u59312(a)uwe> wrote:

>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.
--

John W. Vinson [MVP]
From: SamMexico via AccessMonster.com on
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));

--
Message posted via http://www.accessmonster.com

From: John W. Vinson on
On Tue, 27 Apr 2010 08:06:46 GMT, "SamMexico via AccessMonster.com"
<u59312(a)uwe> wrote:

>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.
--

John W. Vinson [MVP]
 |  Next  |  Last
Pages: 1 2
Prev: countdown timer
Next: Issue with code SendObject 2