Building a query to display a count of leading digits in a dataset

[DerekW]

Hello,

I am in need of assistance with building an Access query that will display a count of the number of times that a specific number is the leading digit in the Amount column in the attached dataset. For example, I am looking for a query that will tell me the number of times that "1" is the leading digit of the amount, that "2" is the leading digit of the amount, that "3" is the leading digit of the amount, and so on all the way to 9.

I have attached a sample workbook to use with the data in it. Please let me know if you have any questions.

Thanks,
Derek

[pbaldy]

This type of thing?

SELECT Count(*) AS CountOfAmount
FROM Transactions
WHERE Left([Amount],1)="1"

[pbaldy]

Or this type of thing:

SELECT Left([Amount],1), Count(*) AS CountOfAmount
FROM Transactions
GROUP BY Left([Amount],1)

[DerekW]

That second option works well for me, thank you. Now if I wanted to divide the count of each category by the total number of records, how would I do that? Put another way, how do I show the percentage amount of 1 as the leading digit out of all the records?

[pbaldy]

I would do that in the form/report the query is being used for. You can do it in the query with a subquery to get the total, but it could be a performance nightmare.

[DerekW]

Okay, I have figured out how to give me that answer on percentages. Thanks for your help, solved my problem!

[pbaldy]

Happy to help!