Best data structure?? 1

[bhoran]

I am currently looking to develop a database for a sales and marketing forecast evaluation tool.

I am currently looking at a table that has similar fields to the following2 examples)

1. item#; description; account; (then values) ACT01; ACT02; ACT03; PLN01; PLN02; PLN03 etc etc.

There are 5 plans (ACT, PLN, AOP, etc) each consisting of 18 months (not 3 as shown above) this gives a total of 90+ fields which not only seems alot but makes a huge amount of work when it comes to reports as I will need a different report for each month.

I have been looking at having the following structure instead and using cross-tab queries to roll up to a column styled report but I don't seem to be able to cross-tab both the value and the plan.

2. item#; description; account; Plan; Value; Mth;

Can anyone give me guidance here. I have used Accounting software and report writer before and I would expect the data to be stored as in example 2.

 

[SkipVought]

Hi,

item#; description; account; Plan; Value; Mth

is DEFINITELY the way to go!

What can't you seem to do with at crosstab. Give me an example of source and result data.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bhoran]

Raw data similar to this:

Item Desc Account FY Amt Mth Plan

BP BACKPACKS CONVENIENT 2004 10 1 ACT
BP BACKPACKS CONVENIENT 2004 12 2 ACT
BP BACKPACKS CONVENIENT 2004 5 1 AOP
BP BACKPACKS CONVENIENT 2004 7 2 AOP
CC100 CC 100 BOLES 2004 10 1 ACT
CC100 CC 100 BOLES 2004 10 2 ACT
CC100 CC 100 BOLES 2004 15 1 AOP
CC100 CC 100 BOLES 2004 10 2 AOP

What I need in the report is the data by plan and by month in each column e.g.
(mth 1) (mth 2)
Item Desc Account FY ACT AOP ACT AOP
BP BACKPACKS CONVENIENT 2004 10 5 12 7

Ineed them by column as I will add a Variance columns for comparatives.

I can do it for one month at a time, or aggregate a number of months, no problem but if I want to compare several months over different plans side by side (as above) I have difficulty.

my results (cross tab 1 using mth):
Item Descr Account Plan TtlAmt FY 1 2

BP BACKPACKS CONVENIE ACT 20 2004 10 10
BP BACKPACKS CONVENIE AOP 10 2004 5 7
CC100 CC 100 BOLES ACT 20 2004 10 10
CC100 CC 100 BOLES AOP 10 2004 15 10

(crosstab 2 using Plan)
Item Desc Account Mth FY TtlAmt ACT AOP
BP BACKPACKS CONVENIENT 1 2004 15 10 5
BP BACKPACKS CONVENIENT 2 2004 19 12 7
CC100 CC 100 BOLES 1 2004 25 10 15
CC100 CC 100 BOLES 2 2004 20 10 10

I am quite happy to build worktables if that will work but it seems to have me stumped.

 

[SkipVought]

here's an Excel PivotTable result using your raw data

Sum of Amt      Mth     Plan			
                1               2              Grand Total
Item    FY      ACT     AOP     ACT     AOP
BP      2004    10      5       12      7       34
CC100   2004    10      15      10      10      45
Grand Total     20      20      22      17      79

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bhoran]

I was hoping to do all the reporting within Access. Is it possible to run a report that acts like or reports from an Excel Pivot table but appears to come from within Access? I know you can put a pivot table within a form - perhaps that is the best way to go?

Cheers

 

[bhoran]

I have another thread running asking how I can turn off the windows standard resize and close buttons when a report is in print preview mode in MS Access 2000, I thought if you accessed the report through a form where these buttons were suppressed it automatically suppressed them but that does not seem to be the case.

I do you know how that is done?

Cheers