DCount


A

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
 
Ad

Advertisements

J

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.
 
J

John Spencer (MVP)

I'm sorry. I do not understand.

Did my suggestion work? Or did it fail?
 
A

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
 
M

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
 
Ad

Advertisements

A

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?

.

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DCOUNT or not DCOUNT, that is the question 2
Dcount 2
Dcount 4
Dcount 0
Dcount 4
Dcount 1
DCount 4
DCount with a Context 2

Top