Results 1 to 3 of 3
  1. #1
    Pluto
    Guest

    Module Question (97 SR-2)

    I have a function that calculates the Geometric Mean over an entire group (returns 1 row with this value). What I need it to do now is step through my query and for each value in the "attmd" field, calculate the geometric mean. So I would get back a list of "attmd" with their geometric mean in the next column. This is what I have right now

    Function GeoMean2()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Dim LOS As Field, Product As Variant
    Dim num As Variant, Tot As Variant
    Dim Rcount%

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("QryMedStafflist")
    Set rst = qdf.OpenRecordset
    Set LOS = rst.Fields("LOS")
    Rcount% = rst.RecordCount
    Tot = 1
    While Not rst.EOF
    num = Tot * LOS
    rst.MoveNext
    Tot = num
    Wend
    Produin the next column. This isct = Tot
    GeoMean2 = Format((Product ^ (1 / Rcount%)), "#0.00")

    End Function

    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Module Question (97 SR-2)

    Sorry, but are you trying to store the geometric mean in a table or what? You're opening a recordset based on a saved query, "QryMedStafflist", but then setting a variable to a value. What exactly are you attempting to accomplish? Are you trying to see the means in a query?
    Charlotte

  3. #3
    Pluto
    Guest

    Re: Module Question (97 SR-2)

    I have one query that takes data from a several tables (QryMedStaffList). Now in my second query, I want to group by the "attmd" field (which is attending physician) and then show each physician's geometric mean LOS (length of stay). If I specify one physician in QryMedStaffList, it comes back with his/her row in the second query with the corresponding mean. If I don't specify one physician, it returns all physicians with the geometric mean of the group repeated for each physician, instead of each physician's mean. (does that make sense?)

Posting Permissions

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