Count Function

[JoeL]

Hello!

I created a table called Lotto and entered the winning
numbers. My question is how to create queries that.
1. count the occurence of '1' on Field1
2. count occurence of '2' on Field1 & Field2
3. count occurence of '3' on Field1, Field2, and Field3
4. count occurence of '4' on Field1, Field2, Field3 &
Field4
5. count occurence of '5' on Field1, Field2, Fied3,
Field4, and Field5
6. Count the occurence of each numbers 6 thru 45 on all
fields (Field1 thru Field5)

I guess you get the idea of what I am trying to do.

Thanks.


Thanks.

 

[Michele Zenna]

Can you explain more what you want to do.
Thanks

 

[Guest]

I want to count how many 1's or 2's, etc. on all the
fields (field1 thru field5) and all the records. Or how
many times the number 1 or 2, etc was picked up.

 

[Tom Ellison]

Dear Joel:

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*"

Are your Field1, Field2, etc. all text columns? If not, you may need:

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

I don't know what you mean by the 6 thru 45 part. Are you looking for
numbers that contain "45" - that is two digits and in that order?

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