Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I'm attempting to create a weighted total percentage in an Access ptivottable ... with no luck. I've found lots of refrences to the task in Excel, but not for Access and I've not been able to successfully apply the Excel instructions. Can anyone point me to some step-by-step Acess instructions?

    Thanks for the help,
    Marty


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could calculate the weighted average in a query, and base the pivot table on the query.

    (Personally, I avoid using pivot tables in Access)

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    After a lot of trial and error I came up with an acceptable solution to my problem by:
    1) adding my two fields (my_numerator_data & my_denominator_data) to be used in the weighted calculations to the Pivottable detail section,
    2) using AutoCalc to add a Sum to each of these columns,
    3) using Formulas - 'Create Calculated Detail Field' to add the basic calculation [my_numerator_data] / [ my_denominator_data],
    4) using Formulas - 'Create Calculated Total' to add the subtotals/totals calculation: [my_numerator_data] (Total) / [ my_denominator_data] (Total),
    5) changed the column width to zero for the two fields added in step 1.

    When Show Details has been selected, the calculation from step 3 is displayed and when Hide Details is selected the calculation from step 4 is displayed. While the detail calculation from step 3 was fairly obvious to me, it took me a while to discover and use the "totals" fields in 'Create Calculated Total' Properites-Dialog field droplist which were created as a result of step 2.

    I hope some else can benefits from my learning experience.

Posting Permissions

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