Counting check boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
..
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top