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

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" <Dean DeleteThis @discussions.microsoft.com> wrote in message
news:52FD1D52-3413-428A-A0E1-899FC580FBF3@microsoft.com...
> 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