reading in an access table using the code builder

[AndrewWolford]

Hi, I have a question that doesnt seem to be too difficult...(famous last words) I have an access table that contains a list of everyone's age and their account balances for 19 different accounts. I need to find the average age of the persons with balances by each account (for example the average age of people invested in account 5 is 43). I want to use the code builder for a command button on a form to read in the table and then compute the average age. I know how to do this as far as pseudo code, i just dont know the sytax, this is what I was thinking it would look like.

Private Sub command1_click()
Open the table for importing
Do until EOF
if account > 0 (only want peope w/ account balances)
numPeople = numPeople + 1
totalAge = totalAge + age (where age is the age field
in the table)
end if
loop
aveAge = totalAge/numPeople

Can anyone help with the actual syntax? Thanks for any help to this rather long-winded question....

-Andrew

 

[WildHare]

SELECT Account, Avg(Age)
WHERE balance>0
GROUP BY Account;



Should give you:

Account Average Age
------- -----------
A 23
B 32
C 40


Its not as difficult as you might think...



Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm