Legends
in [Forms]
|
Prev: countdown timer
Next: Issue with code SendObject 2
From: SamMexico via AccessMonster.com on 26 Apr 2010 05:54 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 26 Apr 2010 11:00 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 26 Apr 2010 11:31 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 27 Apr 2010 04:06 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 27 Apr 2010 12:27
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] |