Thread: Weighted Average Function by subgroup

1. Registered User
Join Date
May 2005
Posts
28

Unanswered: Weighted Average Function by subgroup

I have VBA weighted average custom functions that work great on entire tables. I'm wondering if there is an object that defines the portion of the table segregated by groups setup in the detail portion of a form?

Here's the wtgavg function I use for whole tables. Thanks!

Exp = Numbers to average
Crit = Weighting
Dmn = Table/Query name

Code:
```Public Function WtgAvg(Exp As String, Dmn As String, Crit As String) As Double
On Error GoTo errorhndl:

Dim Num As Double
Dim Den As Double
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(Dmn)

rst.MoveFirst

Num = 0
Den = 0
Do Until rst.EOF
Num = Num + CDbl(rst.fields(Exp).Value) * CDbl(rst.fields(Crit).Value)
Den = Den + CDbl(rst.fields(Crit).Value)
rst.MoveNext
Loop

WtgAvg = Num / Den

errorhndl:
rst.Close
Set rst = Nothing

End Function```
Last edited by AhrenL; 11-11-09 at 15:36. Reason: Set as Question

2. Registered User
Join Date
Oct 2009
Posts
340
there is nothing defined as a 'group' within a form. it is within reports.

in forms; you would have to add a field to the underlying table and put in a value that identified which group a record is with.....and then modify your code to accommodate this grouping method.

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Does this give the same result as your function?
Code:
```SELECT SUM(Exp * Crit) / SUM(Crit) AS WtgAvg
FROM myTable```
Obviously you need to replace your table and column names, and coerce them to doubles if needs be. If this works then getting to what you want is trivial.

4. Registered User
Join Date
May 2005
Posts
28
NTC - Sorry about the form/report confusion. I'm an excel guy still getting my feet wet with Access.

Pootle - I'm a baby when it comes to SQL; so if I put that SQL code into a textbox in a group or detail reports area it will only use the value subset of the group/detail? I'll give it a try. Thanks!

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
No

Open a new query window. Go to SQL view. Paste in the SQL and make the edits you need to suit your table. Run. If the result is correct then we move on the next stages. Ignore forms for now.

6. Registered User
Join Date
May 2005
Posts
28
Okay. That works for the whole table.. Do we use a GROUP BY statement now? How do you run a SQL statement in a text box? Thanks for the help!

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by AhrenL
Do we use a GROUP BY statement now?
Spot on - you are one step ahead of me!
Originally Posted by AhrenL
How do you run a SQL statement in a text box?
But now you are too many steps ahead ;o)

Are you able to figure out from the SQL how to use GROUP BY to get the weighted average for each <whatever your group is>? Whether you can or cannot, post your attempt.
SQL Tutorial

8. Registered User
Join Date
May 2005
Posts
28
Yes. I can get it to display the wtgavg by Grouping.

Code:
```SELECT SUM([QRM OAD] * [QRM Dirty Market Value]) / SUM( [QRM Dirty Market Value]) AS WtgAvg
FROM [Agency CMO Fixed]
GROUP BY [Curr Intent], [Collat Generic], [NWAC];```

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Good stuff. Now our problem. We can get this information, but putting it in to the form is the tricky bit. Is your form read only or do users edit the data?

10. Registered User
Join Date
May 2005
Posts
28
Sorry, never got an email that you replied..

It's actually a report not a form, so that should make it easier. I need the data to appear at the top of each subgroup. Thanks!!!

11. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
In that case we have wasted our time.
Originally Posted by NTC
there is nothing defined as a 'group' within a form. it is within reports.
NTC made the very point days ago. This is trivial in reports. All the hoops we have been jumping through are due to the "limitations" of forms. Details like whether or not you are using forms or reports make all the difference in the world.

Take the bit out of the select clause of the SQL I provided and put it in a textbox in your report grouping. Done.

12. Registered User
Join Date
May 2005
Posts
28
Sorry.. I really wasn't clear (Though it was totally clear in my head when I replied to NTC). Not a complete waste of time as I've definitely learned more about SQL, and I'll have to do this in a form sooner than later in any case.

That's working great however. Sorry for the extra trouble.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•