DCount

[an]

Hello!

I have a table with 3 columns with number data.
I need construct a query to Count the equals values in the
3 columns.
Ex: ColA ColB ColC
0 0 0
Result=3

To single Col I used in field query:

DCount("ColA";"Table";"ColA=0")
And work fine, but to next column I don't know how do it.

Thanks in advance
an

 

[John Spencer (MVP)]

If the three columns ALWAYS have a value then:

SELECT Abs(ColA=ColB + ColA=ColC + ColB=ColC)
FROM Table

If the columns could be blank (null), what do you want to call a match. Is 0
equal to null? Is Null equal to Null?
The solution becomes more complex depending on your answers.

 

[an]

Thanks for your help, but don't work fine.
Thanks.
an

 

[John Spencer (MVP)]

I'm sorry. I do not understand.

Did my suggestion work? Or did it fail?

 

[an]

I'm sorry, too.

Don´t work my query but about your suggestion is not
questionable, Ok?

The problem is wich I have blank cells (nulls) in table
and zeros. If I count Column by Column, it's all right.
But I need to count totals zeros (not nulls values) in 3
Columns of Table.
For example, if A1=3, B1=0 and C1=0 the query count only
the value in B1 but not count 2nd zero inC1. The result=1
were the exact result is 2.

Not count seconds values independentment of position.
I used in query:
Count0: DCount("[IdNumber]";"T_Table";"[ColumnA]=0 or
[ColumnB]=0 or [ColumnC]=0")

and result=1. Why?

Sorry for my explanation, but...

Cordially
an

 

[Michel Walsh]

Hi,


Your DCount returns only 1 because there is only ONE RECORD where the condition hold.


Try the initial John's suggestion, but modified like this:

SELECT CHOOSE( 1+ Abs(ColA=ColB + ColA=ColC + ColB=ColC) , 1, 2, 3, 3)
FROM TableNameHere

So, by implicit enumeration (not really by logic) the result is always right.

Hoping it may help,
Vanderghast, Access MVP



I'm sorry, too.

Don´t work my query but about your suggestion is not
questionable, Ok?

The problem is wich I have blank cells (nulls) in table
and zeros. If I count Column by Column, it's all right.
But I need to count totals zeros (not nulls values) in 3
Columns of Table.
For example, if A1=3, B1=0 and C1=0 the query count only
the value in B1 but not count 2nd zero inC1. The result=1
were the exact result is 2.

Not count seconds values independentment of position.
I used in query:
Count0: DCount("[IdNumber]";"T_Table";"[ColumnA]=0 or
[ColumnB]=0 or [ColumnC]=0")

and result=1. Why?

Sorry for my explanation, but...

Cordially
an

 

[an]

My option:

DCount+Dcount+Dcount

Work Fine.
Many Thanks.
an

-----Original Message-----
Hi,


Your DCount returns only 1 because there is only ONE

RECORD where the condition hold.

Try the initial John's suggestion, but modified like this:

SELECT CHOOSE( 1+ Abs(ColA=ColB + ColA=ColC + ColB=ColC) , 1, 2, 3, 3)
FROM TableNameHere

So, by implicit enumeration (not really by logic) the result is always right.

Hoping it may help,
Vanderghast, Access MVP



I'm sorry, too.

Don´t work my query but about your suggestion is not
questionable, Ok?

The problem is wich I have blank cells (nulls) in table
and zeros. If I count Column by Column, it's all right.
But I need to count totals zeros (not nulls values) in 3
Columns of Table.
For example, if A1=3, B1=0 and C1=0 the query count only
the value in B1 but not count 2nd zero inC1. The result=1
were the exact result is 2.

Not count seconds values independentment of position.
I used in query:
Count0: DCount("[IdNumber]";"T_Table";"[ColumnA]=0 or
[ColumnB]=0 or [ColumnC]=0")

and result=1. Why?

Sorry for my explanation, but...

Cordially
an

-----Original Message-----
I'm sorry. I do not understand.

Did my suggestion work? Or did it fail?

.

.