If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting check boxes
I'm having a problem as follows:-
My table holds a supplier name, date and some check boxes for the state of each delivery that turns up. I want to produce a graph that shows by supplier/date range the percentage of each field (tick box). i.e. Supplier Date 1 2 3 4 5 ABC123 01/07/2007 X X zyx333 02/07/2007 X ABC123 27/07/2007 X X X ABC123 30/07/2007 X X X If the above data was in the table and the query was run for supplier ABC123 and dates range 01/07/200 to 30/07/2007, it should produce Supplier 1 2 3 4 5 ABC123 2 1 3 2 This data should then be represented on a PIE graph as percentages. The query I currently have is: SELECT Tbl_Sup_Conf.Supplier, Count(Tbl_Sup_Conf.[1]) AS CountOf1, Count(Tbl_Sup_Conf.[2]) AS CountOf2, Count(Tbl_Sup_Conf.[3]) AS CountOf3 FROM Tbl_Sup_Conf WHERE (((Tbl_Sup_Conf.Date) Between [?Start_Date] And [?End_Date])) GROUP BY Tbl_Sup_Conf.Supplier HAVING (((Tbl_Sup_Conf.Supplier)=[?Supplier]) AND ((Count(Tbl_Sup_Conf.[1]))=True) AND ((Count(Tbl_Sup_Conf.[2]))=True) AND ((Count(Tbl_Sup_Conf.[3]))=True)); Which returns a count of the check boxes not a count of the check boxes that are ticked! I then have the problem of producing the graph! Can anyone help with the query and the graph? Thanks in advance |
#2
|
|||
|
|||
Counting check boxes
Count counts the presence of a value. A checkbox always has a value
(true[-1] or false [0]). Try Count(IIF([1] = True, [1], Null) The above counts the value of the checkbox if the checkbox is true or counts null (no value) if the checkbox is false. OR use Abs(Sum([1])) The above sums the value of checkbox -1 or 0 and then the Abs removes the negative sign. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Dean" wrote in message ... I'm having a problem as follows:- My table holds a supplier name, date and some check boxes for the state of each delivery that turns up. I want to produce a graph that shows by supplier/date range the percentage of each field (tick box). i.e. Supplier Date 1 2 3 4 5 ABC123 01/07/2007 X X zyx333 02/07/2007 X ABC123 27/07/2007 X X X ABC123 30/07/2007 X X X If the above data was in the table and the query was run for supplier ABC123 and dates range 01/07/200 to 30/07/2007, it should produce Supplier 1 2 3 4 5 ABC123 2 1 3 2 This data should then be represented on a PIE graph as percentages. The query I currently have is: SELECT Tbl_Sup_Conf.Supplier, Count(Tbl_Sup_Conf.[1]) AS CountOf1, Count(Tbl_Sup_Conf.[2]) AS CountOf2, Count(Tbl_Sup_Conf.[3]) AS CountOf3 FROM Tbl_Sup_Conf WHERE (((Tbl_Sup_Conf.Date) Between [?Start_Date] And [?End_Date])) GROUP BY Tbl_Sup_Conf.Supplier HAVING (((Tbl_Sup_Conf.Supplier)=[?Supplier]) AND ((Count(Tbl_Sup_Conf.[1]))=True) AND ((Count(Tbl_Sup_Conf.[2]))=True) AND ((Count(Tbl_Sup_Conf.[3]))=True)); Which returns a count of the check boxes not a count of the check boxes that are ticked! I then have the problem of producing the graph! Can anyone help with the query and the graph? Thanks in advance |
#3
|
|||
|
|||
Counting check boxes
Thanks John, the "Count(IIF" did the trick!
One other question though (still related) do you know of any sites that will give me a very basic start to graphing in Access. This data has been put into a graph but I can't understand how it has done it! Thanks again. Dean "John Spencer" wrote: Count counts the presence of a value. A checkbox always has a value (true[-1] or false [0]). Try Count(IIF([1] = True, [1], Null) The above counts the value of the checkbox if the checkbox is true or counts null (no value) if the checkbox is false. OR use Abs(Sum([1])) The above sums the value of checkbox -1 or 0 and then the Abs removes the negative sign. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Dean" wrote in message ... I'm having a problem as follows:- My table holds a supplier name, date and some check boxes for the state of each delivery that turns up. I want to produce a graph that shows by supplier/date range the percentage of each field (tick box). i.e. Supplier Date 1 2 3 4 5 ABC123 01/07/2007 X X zyx333 02/07/2007 X ABC123 27/07/2007 X X X ABC123 30/07/2007 X X X If the above data was in the table and the query was run for supplier ABC123 and dates range 01/07/200 to 30/07/2007, it should produce Supplier 1 2 3 4 5 ABC123 2 1 3 2 This data should then be represented on a PIE graph as percentages. The query I currently have is: SELECT Tbl_Sup_Conf.Supplier, Count(Tbl_Sup_Conf.[1]) AS CountOf1, Count(Tbl_Sup_Conf.[2]) AS CountOf2, Count(Tbl_Sup_Conf.[3]) AS CountOf3 FROM Tbl_Sup_Conf WHERE (((Tbl_Sup_Conf.Date) Between [?Start_Date] And [?End_Date])) GROUP BY Tbl_Sup_Conf.Supplier HAVING (((Tbl_Sup_Conf.Supplier)=[?Supplier]) AND ((Count(Tbl_Sup_Conf.[1]))=True) AND ((Count(Tbl_Sup_Conf.[2]))=True) AND ((Count(Tbl_Sup_Conf.[3]))=True)); Which returns a count of the check boxes not a count of the check boxes that are ticked! I then have the problem of producing the graph! Can anyone help with the query and the graph? Thanks in advance |
#4
|
|||
|
|||
Counting check boxes
No ideas for that. Sorry.
-- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Dean" wrote in message ... Thanks John, the "Count(IIF" did the trick! One other question though (still related) do you know of any sites that will give me a very basic start to graphing in Access. This data has been put into a graph but I can't understand how it has done it! Thanks again. Dean "John Spencer" wrote: Count counts the presence of a value. A checkbox always has a value (true[-1] or false [0]). Try Count(IIF([1] = True, [1], Null) The above counts the value of the checkbox if the checkbox is true or counts null (no value) if the checkbox is false. OR use Abs(Sum([1])) The above sums the value of checkbox -1 or 0 and then the Abs removes the negative sign. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Dean" wrote in message ... I'm having a problem as follows:- My table holds a supplier name, date and some check boxes for the state of each delivery that turns up. I want to produce a graph that shows by supplier/date range the percentage of each field (tick box). i.e. Supplier Date 1 2 3 4 5 ABC123 01/07/2007 X X zyx333 02/07/2007 X ABC123 27/07/2007 X X X ABC123 30/07/2007 X X X If the above data was in the table and the query was run for supplier ABC123 and dates range 01/07/200 to 30/07/2007, it should produce Supplier 1 2 3 4 5 ABC123 2 1 3 2 This data should then be represented on a PIE graph as percentages. The query I currently have is: SELECT Tbl_Sup_Conf.Supplier, Count(Tbl_Sup_Conf.[1]) AS CountOf1, Count(Tbl_Sup_Conf.[2]) AS CountOf2, Count(Tbl_Sup_Conf.[3]) AS CountOf3 FROM Tbl_Sup_Conf WHERE (((Tbl_Sup_Conf.Date) Between [?Start_Date] And [?End_Date])) GROUP BY Tbl_Sup_Conf.Supplier HAVING (((Tbl_Sup_Conf.Supplier)=[?Supplier]) AND ((Count(Tbl_Sup_Conf.[1]))=True) AND ((Count(Tbl_Sup_Conf.[2]))=True) AND ((Count(Tbl_Sup_Conf.[3]))=True)); Which returns a count of the check boxes not a count of the check boxes that are ticked! I then have the problem of producing the graph! Can anyone help with the query and the graph? Thanks in advance |
Thread Tools | |
Display Modes | |
|
|