Results 1 to 12 of 12
  1. #1
    Join Date
    May 2005
    Posts
    28

    Question 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. #2
    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. #3
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    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. #5
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    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. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by AhrenL View Post
    Do we use a GROUP BY statement now?
    Spot on - you are one step ahead of me!
    Quote Originally Posted by AhrenL View Post
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    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. #9
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    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. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case we have wasted our time.
    Quote Originally Posted by NTC View Post
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    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
  •