Welcome to SPN

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

Sign Up Now!
  1. Monthly (Recurring) Target: $300 :: Achieved: $95
      Become a Supporter    ::   Make a Contribution   

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!
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page