Counting "yes" in multiple columns

[Guest]

I cannot seem to figure this out. I have a table with about 13 yes/no text
fields. I would like to create a query that returns the number of yesses for
each field. I have tried the following:

Field: Fieldname
Table: Table1
Total: Expression
Criteria: Abs("Sum[Receiving care elsewhere]"=True)

(I did not put in the quotation marks myself - they automatically appeared.
I keep receiving the response that "You tried to execute a query that does
not include the specified expression 'Receiving care elsewhere' as part of an
aggregate function." Receiving care elsewhere is one of my field names.)

Thanks!

 

[MGFoster]

I cannot seem to figure this out. I have a table with about 13 yes/no text
fields. I would like to create a query that returns the number of yesses for
each field. I have tried the following:

Field: Fieldname
Table: Table1
Total: Expression
Criteria: Abs("Sum[Receiving care elsewhere]"=True)

(I did not put in the quotation marks myself - they automatically appeared.
I keep receiving the response that "You tried to execute a query that does
not include the specified expression 'Receiving care elsewhere' as part of an
aggregate function." Receiving care elsewhere is one of my field names.)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Format like this:

Criteria: Sum(Abs([Receiving care elsewhere]))

In JET (Access' db engine) True = -1 and False = 0; therefore, you don't
need the comparison to True (=True) in the expression, since

Sum(Abs(-1)) = 1.

If your needs are pretty simple, you could use a more SQL-like query:

SELECT Count(*)
FROM table_name
WHERE [Receiving care elsewhere] = True

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjCKw4echKqOuFEgEQK56gCgh+RzcycuLOTijhh3lFf/TyzdktoAniJn
K9whWhwN5oLnvKBoscM3WniB
=8k09
-----END PGP SIGNATURE-----

 

[Guest]

The solution is the following

You are summing 0 and -1 as mgfoster mentioned so try

sum(abs(val([Field])))

- Raoul

 

[John Spencer (MVP)]

Field: SumFieldName: ABS(SUM([TableName].[FieldName]))
Criteria: <NONE>


Field: Abs(Sum([Receiving care elsewhere]))