Great deal of trouble doing analysis in reports

[bhoran]

I am building a report to analyse data.

I have a table with data similar to below:
PG PrGroup Account FY Amt Period LD
BP BBBPack BBBVENIENT 2004 10 1 ACT
BP BBBPack BBBVENIENT 2004 12 2 ACT
BP BBBPack BBBVENIENT 2004 5 1 AOP
BP BBBPack BBBVENIENT 2004 7 2 AOP
BP BBBPack BBBVENIENT 2004 8 1 NFY
BP BBBPack BBBVENIENT 2004 9 2 NFY
CC100 BBB100 BBBES 2004 10 1 ACT
CC100 BBB100 BBBES 2004 10 2 ACT
CC100 BBB100 BBBES 2004 15 1 AOP
CC100 BBB100 BBBES 2004 10 2 AOP
CC100 BBB100 BBBES 2004 12 1 NFY
CC100 BBB100 BBBES 2004 11 2 NFY

I have then used a cross query to produce a format in period by period buckets aggregated by Plan:

PG PrGroup LD FY TtlAmt 1 2
BP BBBPack ACT 2004 22 10 12
BP BBBPack AOP 2004 12 5 7
BP BBBPack NFY 2004 17 8 9
CC100 BBB100 ACT 2004 20 10 10
CC100 BBB100 AOP 2004 25 15 10
CC100 BBB100 NFY 2004 23 12 11

What I want to do now is in a report compare each plan eg (ACT - AOP)/AOP to get a % difference.

e.g

BP BBBPack
ACT 2004 22 10 12
AOP 2004 12 5 7
NFY 2004 17 8 9

ACTvAOP 50% 42%


CC100 BBB100
ACT 2004 20 10 10
AOP 2004 25 15 10
NFY 2004 23 12 11

ACTvAOP -30% 0%



I have been trying =DSum([1],[tablename],[LD]="AOP&quot both in a expression in the Query and directly in the report but I can't seem to get it to work. (If I can get the 1st part right I can build the rest of the formula).

Am I going about this the right way? Am I using the correct formula? Do I need to build a workfile table aand do the calculations there each time the report runs?

Sorry I am a bit new to this any help would be great!

Cheers.

 

[bhoran]

Is it even possible to handle this within a report??

Please Help!

 

[dhookom]

I think this is the third thread you have started with basically the same question. If I am wrong please let us know. It is always best to keep a question in one thread.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.