monthly percentage calculations

[ali32j]

Hi All

I have a table in format below:

CustomerName Calendar JAN FEB MAR
customer 1 YTD £57.59 £54.38 £3.54
customer 1 Y-1 £36.60 £32.56 £274.04
customer 2 YTD £20.05 £5.80 £126.85
customer 2 Y-1 £342.20 £103.94 £126.85

I m looking to add a new layer to reflect percentage increase on last year so the table will look something like:

customer 1 YTD £57.59 £54.38 £3.54
customer 1 Y-1 £36.60 £32.56 £274.04
customer 1 Perc 57.3% 67.0% -98.7%
customer 2 YTD £20.05 £5.80 £126.85
customer 2 Y-1 £342.20 £103.94 £126.85
customer 2 Perc -94.1% -94.4% 0.0%

the percentage is calculated using the following formula

perc= ((YTD-Y-1)/Y-1)x100

really struggling as to how i start this, i know i need an append query, and also need to use query to calculate but i m not sure how to start, could anyone offer some advice on how to get the calculations, it very easily done in excel but not sure where to start in access query...

Ali

 

[SkipVought]

Hi,

You are doing things with your table that is not best and accepted practices.

First, you data is not normalized.

Second, aggregations ought not to be stored in tables. Aggregations can be calculated in a query at report time.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}