Calculating field Pivot table?

[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!

 

[strive4peace]

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

*