Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculating field Pivot table?

Discussion in 'Information Technology' started by ANGELICA, Jul 28, 2006.

  1. ANGELICA

    ANGELICA
    Expand Collapse
    Guest

    Hi,
    I have a Query with the following Columns:: Client_Name, Month, Total_trans
    Per month. I'm seeing this query as a Pivot Table and I'm trying to find a
    way to insert a customized calculating field that tells me the % of variation
    of Total_trans Per each month.Basically what I'm looking for is tho know
    which customers presented a high percentage of variation on the total of
    trans from one month to another.
    I don't have any idea how to approach this....any clue how?
    Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Calculating the Rarity of a Fingerprint Breaking News Dec 13, 2010
    Canada Huddersfield Sikh community leader Bakhshish Singh Bhullar dies... Breaking News Jan 27, 2014
    The ‘Flying Sikh’ on India’s lamentable track-and-field prowess Sports & Fitness Oct 4, 2010
    India India Can Never Beat China in Any Field Breaking News May 22, 2010
    US Defense Department Hiring Resident Aliens in Medical and Language Fields Business, Lifestyle & Leisure Dec 6, 2008

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Angelica,

    first, have you tried using functions available when you
    turn on the Totals row -- ie: Min, Max, Avg, Var, StDev?

    if you want to calculate the difference between 2 months,
    you can do this:

    for reference, I will call your first query Q1

    make another query and use 2 copies of Q1 ... copy 1 is
    Q1_current and copy 2 is Q1_previous

    link on client
    (you really should be using a ClientID, not Client_Name...)

    on the grid:

    field --> Month
    table --> Q1_previous
    criteria --> Q1_current.month -1

    now, you can add and subtract values from one month to the next

    field --> trans_cur: [Total_trans Per month]
    table --> Q1_current

    field --> trans_prev: [Total_trans Per month]
    table --> Q1_previous

    field --> Diff: trans_cur - trans_prev

    hopefully, this will shed some light on what you need and
    you can take it from here

    If your Month field is not a number, you can pull the year
    and month number out of a date:

    field --> MonthNum: Month([datefield])
    field --> Yr: Year([datefield])

    "Month" is a reserved word and should not be used for a name


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    ANGELICA wrote:
    > Hi,
    > I have a Query with the following Columns:: Client_Name, Month, Total_trans
    > Per month. I'm seeing this query as a Pivot Table and I'm trying to find a
    > way to insert a customized calculating field that tells me the % of variation
    > of Total_trans Per each month.Basically what I'm looking for is tho know
    > which customers presented a high percentage of variation on the total of
    > trans from one month to another.
    > I don't have any idea how to approach this....any clue how?
    > Thanks!
     

Share This Page