getting an average age using SQL

[AndrewWolford]

On 4/5 WildHare suggested the following SQL statement in answer to my thread "reading in an access table using the code builder" to get an average age by account:

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

First, thanks for the help. Secondly, I have a follow-up question... This pretty much works, however I've run into a small problem which I believe is due to the way the table is laid out. here's what it looks like:

SSN AGE Acct1Bal Acct2Bal .... Acct19Bal

... 20 100 0 0
... 30 0 100 0
... 40 0 0 100
and so on....

My SQL statement looks like this:

SELECT Ave(Age) FROM Accounts WHERE Acct1Bal>0;

this returns the ave age for account1, which is great but I'll have to make 19 queries (1 for each account) doing it this way. Is there a way to get all the ave. ages for each account in 1 query? I don't think I can use a GROUP BY Account since 'Account' and 'balance' aren't seperate. I realize this is a pretty poor table layout, but can it be done? THanks!

-Andrew

 

[CosmoKramer]

Andrew,

You're right, it is pretty poor design. This should work with what you've got in the meantime:

SELECT Avg(Age) FROM Accounts WHERE Acct1Bal>0 or Acct2Bal>0 or Acct3Bal>0 or Acct4Bal>0.......etc.....;

 

[WildHare]

And Andrew, whack yourself 19 times (1 x each account) on the head with Chris Ullman's "Principles of Database Design", 2nd Ed. , Harcourt Brace Jovanovich, NY, NY, 1983..



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

http://www.forumco.com/jmhare

More Access stuff at

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

 

[AndrewWolford]

Thanks for the help guys, and it wasnt me who designed these tables, I know better...(or at least I like to think I do)

 

[AndrewWolford]

Spoke too soon....

I hate to be the bearer of bad news but it didn't work... when I add ... WHERE Acct1Bal>0 or Acct2Bal>0 ...etc...; it still only returns 1 average age. Only with all the 'OR' clauses it gives be the average age of anyone that has a balance in any of the accounts. What I need is the average age of individuals by account. So the end result will have the following information: Average age for acct1 is 55, average age for acct2 is 43, ...acct3 is 32... all the way to acct19. In other words there will be 19 seperage averaged ages. Clear as mud? And like I said before, I'm not even sure if this will be possible with the way the table is laid out, but thanks for any suggestions.

-Andrew