Newbie: Help with Roll-up

  • Thread starter Thread starter Vic
  • Start date Start date
V

Vic

I'm a newbie and my Access class didn't cover this.

I have 3 tables with product forecast data (13 columns => product ID and 12
months, 28 rows => products). I need to create a roll-up that sums the
contents of each "cell" to a corresponding roll-up cell on a 4th table. Can
you suggest an approach? I'm not sure how to begin.

Actually, there is an additional table that has product ID and product name,
but that is not the issue.

This exercise will be the start of a process that slices-and-dices the
forecast. In all there will be 9 tables of product forecast data showing
sales from various sources and regions, but I figured 3 sources would be a
good place to begin.

Ideas?
 
Are you stuck on your "spreadsheet" style table of one field per month? A
better solution would have three columns
tblProdForecast
===================
ProductID
TheMonth
Forecast

This allows you to sum any date range quite easily. Also, it generally isn't
a good idea to store a value that can easily be calculated in a query.
 
Duane,

Regarding the storing of easily calculated values, I certainly agree that a
report or query makes more sense generally. As an overriding approach, I was
thinking of making the process familiar to our sales analyst who is
Excel-centric. Carrie creates the forecasts and actuals and then distributes
them in this "nine table" mode. What she does is extremely labor-intensive
and prone to error. "When you have only a hammer, every problem looks like a
nail."

Am I stuck on the "spreadsheet" style table .... No, but that is what the
source data looks like. I like the idea of normalizing the data with the
3-column format, and I can figure out a way to normalize the data on input.
(It certainly makes adding a 4th and 5th column easy -- "Forecast revenue"
and "Actual Revenue".) How would you recommend dealing with the other
dimension "Sales Source"? The full explanation is that there are 28
trackable product families, each of which can be sold through 9 distinct
sales channels over a 12-month period. We need to view product performance
(monthly/quarterly/annual forecast and actual performance), sales channel
performance and total company performance.

Thank you for helping me think through the challenges.

Vic
 
You can add a field for Sales Channel. If each product is a member of one
family, add the Product Family ID to the Product Table.
 
Back
Top