Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Panchovia

Programmer
May 6, 2010
48
CW


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)

 
A starting point:
SQL:
SELECT Right(Drawnumber,2) AS Field1, Count(*) AS TotalHits
FROM yourTable
GROUP BY Right(Drawnumber,2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Did you try:

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

Have fun.

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top