Oh VERY Dear

[MOTOWN44]

i know this is probably really simple i just cant get my head round it

i have 3 tick boxes on a form called "Form", Field1, 2, and 3

i need a calculated field or a field of some sort that counts per line the number of instances of my tick boxes that are true. Ie if 1 and 3 are ticked the field result is a 2

ive tried playign on with queries in SQL view along the lines of

SELECT
(SELECT Count([Field1]) As Sum[Field1]
FROM Table
WHERE [Field1]=True) +

(SELECT Count([Field2]) As Sum[Field2]
FROM Table
WHERE [Field2]=True)

FROM Table;

but to no avail, ive also tried to use combinations of Count IIF and SUM in fields on a form but i keep getting the number of times field 1 is checked in the entire table and not that specific record.

just to make things interesting...the people im making this for want the information displayed in a table. i know that pretty much isnt possible so im going to cheat and do a form in datasheet style.

this way i think is easier as you can have calculated field that arnt in a table...might be wrong tho

Thanks

 

[Mr. B]

I am assuming that you have a table where your data is being stored. If so, take a look at this query (sql statement):

SELECT IIf([Field1]=-1,1,0) AS FirstField, IIf([Field2]=-1,1,0) AS SecondField, IIf([Field3]=-1,1,0) AS ThirdField, [FirstField]+[SecondField]+[ThirdField] AS Totals
FROM Table1;

The concept here is to create your query with the three yes/no fields producing a 1 or a zero based on whether or not the check box is checked or not. If it is checked then the field in the query for the FirstField (Field1) will creat a 1. This concept is repeated for each of the yes/no fields. Then the last column of the query just adds the values from the other three columns together.

As a rule, you never store calculate value. You always do a calculation to get the value when you need them. (I have seen a couple of exceptions to this rule, but the exceptions are very rare.)

 

[wilpeter]

I used Table1TBL as the named table and Field1, Field2, Field3 as the yes/no checks.
This would be the SQL to get the Sums...
SELECT Table1TBL.ExampleNo, Table1TBL.Field1, Table1TBL.Field3, Abs([field1]+[field2]+[field3]) AS [Sum]
FROM Table1TBL
WHERE (((Table1TBL.Field1)=Yes)) OR (((Table1TBL.Field2)=Yes)) OR (((Table1TBL.Field3)=Yes));

 

[MOTOWN44]

Both methods work perfectly

is there a way of showing the final result in a form that has the record source set to my table tho?

Table is called Table1 and Form is Called Form1

 

[wilpeter]

Both methods work perfectly

is there a way of showing the final result in a form that has the record source set to my table tho?

Table is called Table1 and Form is Called Form1

But why not change the source of the form to the Query?