Count Function

[Joel]

Dear Tom,

Your solution below will not give me the right count for
numbers 1, 2, 3, and 4 because *1* can be
21,31,41,10,11,12,etc same is true with 2, and 3, and 4.

My question was how many 1 were in all the fields, 2 in
all the fields, etc. I tried Field1='1' but gave me 0
result.

Your solution:
SELECT COUNT(*)
FROM YourTable
WHERE Field1 LIKE "*1*"

SELECT COUNT(*)
FROM YourTable
WHERE Field1 & Field2 LIKE "*2*"

SELECT COUNT(*)
FROM YourTable
WHERE Field1 & Field2 & Field3 LIKE "*3*"


Thanks

 

[Tom Ellison]

Dear Joel:

I misunderstood your need. Your post says to look for 1 in the first
column, 2 in the first 2 columns, etc.

Try something like:

SELECT COUNT(*)
FROM YourTable
WHERE 1 IN (Field1, Field2, Field3)

Or however many columns you want to look in.

If the columns are text, put the 1 in quotes.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

It works. Thanks a lot

-----Original Message-----
Dear Joel:

I misunderstood your need. Your post says to look for 1 in the first
column, 2 in the first 2 columns, etc.

Try something like:

SELECT COUNT(*)
FROM YourTable
WHERE 1 IN (Field1, Field2, Field3)

Or however many columns you want to look in.

If the columns are text, put the 1 in quotes.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.