Second part to my earlier SQL questions...

[AndrewWolford]

PLEASE SEE MY PREVIOUS THREAD ABOUT AVERAGING FIELDS USING SQL.

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

 

[kevinclark]

looked at your earlier post and looks like WildHare's SQL should work for you ?

If table Layout is similar:

Account Balance ClientAge
Act1 400 23
Act1 34 35
Act2 378 45
Act3 1045 51
Act3 5566 55

with code something like:

Sub Test_SQL()
Dim dbAccount as DAO.Database
Dim rsAccount as DAO.Recordset
Dim SQL as String
Dim strTmp as String

SQL = "Select Account, Avg(ClientAge) as AvgAge"
SQL = SQL & " From YOURTABLE"
SQL = SQL & " Where Balance > 0"
SQL = SQL & " Group By Account"

Set dbAccount = OpenDatabase(YOUR DATABASE)
Set rsAccount = dbAccount.OpenRecordset(SQL)

While rsAccount.EOF = False
strTmp = strTmp & Fields(0).value & "-" & Fields(1).value
strTmp = strTmp & chr$(13)
rsAccount.MoveNext
Loop

Msgbox strTmp
End Sub

Should return:

Act1-29
Act2-45
Act3-53
etc ....

 

[AndrewWolford]

I think the problem is with the table layout... Instead of looking like what you posted above (which is what it should look like) it looks like this...

SSN ClientAge Acct1Bal Acct2Bal Acct3Bal
... 23 100 1000 1000
... 40 0 0 5000
... 54 100 0 0
and so on...

So because its laid out this way, I can't just group by account. Its a pretty stupid way of creating a table but that's what I have to live with.... Do you think there is any way of doing this in only 1 query instead of a seperate one for each account?

Thanks, Andrew

 

[WildHare]

Andy, I tried doing this in just one query last night and couldn't get it to come out right the way you have it designed and then I got a headache, so I stopped... We really need to think of a way to turn the table 'sideways', so to speak. I'll see if I can't come up with something... 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 going to all this trouble....Also, I can always just create seperage queries for each account if need be. I was just hoping there might be a more efficient way of doing it...Again thanks for all the help!

 

[kevinclark]

I don't know if this is a viable way for you but on a long a shot here is a way of sorting it all out by code.

Sub Button1_Click()
Dim dbTest As DAO.Database
Dim rsTest As DAO.Recordset
Dim i As Integer
Dim AcctArray 'hold occurance of Bal > 0
Dim AgeArray 'hold sum of ages


'If Field(2) is an Acct1Bal field and if Bal > 0 then
'ActArray(2) = Current value + 1
'AgeArray(2) = Current value + Age
'Thus Average of Acct1Bal is AgeArray / ActArray

ActArray = Array(0, 0, 0, 0, 0, 0)
AgeArray = Array(0, 0, 0, 0, 0, 0)

Set dbTest = OpenDatabase("C:\Development\Database\TEST\TEST.mdb&quot
Set rsTest = dbTest.OpenRecordset("Select * From AcctTable&quot

Do While rsTest.EOF <> True
For i = 0 To rsTest.Fields.Count - 1
If rsTest.Fields(i).Name Like &quot;Acct*Bal&quot; Then
If rsTest.Fields(i).Value > 0 Then
ActArray(i) = ActArray(i) + 1
AgeArray(i) = AgeArray(i) + rsTest.Fields(&quot;AGE&quot.Value
End If
End If
Next i
rsTest.MoveNext
Loop

For i = 2 To UBound(ActArray)
MsgBox &quot;Acct&quot; & i - 1 & &quot;Bal average is &quot; & AgeArray(i) / ActArray(i)
Next i
End Sub