Need to Learn VB Data Analysis in Access 2

[Rosberg]

I am attempting to implement an analysis of some transaction information using Benford's Law. This involves the relative frequency of numbers (1's, 2's, etc.) within the left-most digit of each amount. Then the second left-most digit (#0, #1, etc.). Then the two left most digits together (01, 02, etc.).

Apparently it's possible to use Visual Basic to parse the transaction amounts in this way. Could someone suggest a book that I could use to figure out how to implement this process?

Thank you.

 

[richardii]

If I understand what you want to do correctly make a table (with the amount as a text field):

ID Amount
1 1234
2 12333
3 23444
4 666
5 888
6 6785
7 1345
8 16677

run Query1:

SELECT Mid([Table1]![Amount],1,1) AS Expr1
FROM Table1;

and then Query2:

SELECT Query1.Expr1, Count(Query1.Expr1) AS CountOfExpr1
FROM Query1
GROUP BY Query1.Expr1;

to count the frequencies of first digit. To count the second digit use the query below instead (and then run the Query2):

SELECT Mid([Table1]![Amount],2,1) AS Expr1
FROM Table1;

and for the two digits at the start:

SELECT Mid([Table1]![Amount],1,2) AS Expr1
FROM Table1;

I can email the database to you if that would help.

 

[richardii]

In fact you can combine the queries to one:

SELECT Mid([Table1]![Amount],1,1) AS Expr1, Count(Mid([Table1]![Amount],1,1)) AS Expr2
FROM Table1
GROUP BY Mid([Table1]![Amount],1,1);

altering the 1,1 to 2,1 or 1,2 as required.