Counting how many times the right part of a four digit number appear in the database with a query 1

[Panchovia]

I have the following query ms access 2010: field drawnumber should bring up the right part of the numbers 00 to 99 from the 4 digits number in the database
the second field should count how many (records) with the right part of these numbers appears in the database
I used the following code but don't get the correct info in neither of the two fields.

Field1 Drawnumber Field2 TotalHits: Count(Right([Drawnumber],2))
citeria:Right([drawnumber],2)

 

[PHV]

A starting point:

SELECT Right(Drawnumber,2) AS Field1, Count(*) AS TotalHits
FROM yourTable
GROUP BY Right(Drawnumber,2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Panchovia]

thank you very much for the code PHV

 

[Panchovia]

Hi PHV,

How do you get the left part of the numbers 00 to 09 in the database do you have to use the format function with this code.
(using of course the left function)for the other number the code works correctly

Because when you input the number 9 you don't input 0009 you only input 9

 

[Andrzejek]

Did you try:

SELECT Format(Left(Drawnumber,2), "00") AS Field1,

Have fun.

---- Andy

 

[PHV]

I'd use this:
SELECT Left(Format(Drawnumber,"0000"),2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Panchovia]

Thank you PHV your code is correct

Can you combine the two in the same query do you have to change the value of totalhits for the count function because you totalizing on the same field1 but with two different outputs