Do these calculated fields warrant table storage?

[rpw]

Sorry if this is a duplicate but my first attempt to post didn't seem to go
through...

I read that in some cases calculated data should be stored in a table, such
as when the quantity of calculations is very high or when the data is
'point-in-time'. In the following situation, I would like advice on whether
or not to store the calculated data in a table.

All data is currently stored in Excel spreadsheets (and will need to be
imported to Access). There is one workbook per year (10 years or more?), 65
worksheets per workbook, 52 weeks/rows of data per worksheet of which 5
columns/fields are calculated and two of those use a percentage that is not
consistent across all worksheets nor across all workbooks.

So, should I import all calculated data and build a form that will look up
the 'point-in-time' percentage whenever a record is created/modified or do I
rely on a report/query to do the look up and all calculations.

One report would be the equivilent of printing all 65 worksheets in a
workbook, another might be comparing worksheets from different
years/workbooks, and another might be comparing summaries of all worksheets
from different years/workbooks.

All advice is appreciated, thanks in advance...

 

[rpw]

Oh, I might add that the 'point-in-time' percentage would last for a 5 year
period - if that means anything to the design of the tables...

 

[Larry Daugherty]

The only thing I fully comprehend from your description is that you
want to import a bunch of Excel data into Access and some of the Excel
data is the result of calculations. Nothing else is clear. Also, a
"point in time value that lasts only 5 years" is a mystery to me.

If you plan to import every row in your Excel workbooks as a separate
record in your Access database then the rule is fairly simple. If the
components used in the calculated values exist in that same row then
you can safely omit storing calculated results. You can always
calculate them again; now or 10 years from now.

You probably already had this part figured out but since your
workbooks and worksheets within them are organized to reflect their
content then it may be that you want to capture entity types into
separate tables. Make sure that date values go into records, not into
table names. Some of the other organizational markets on the Excel
side are good candidates for going into a lookup table for selection
as an attribute of records.

"Point in time" calculated values are those in which one of the
components is likely to change over time such as the price of an item.

HTH

 

[rpw]

Thank you for the response Larry. And sorry for the lack of clarity. I try
to ask the questions differently.

1) One of the reports that would have to come out of all of the imported
data would have 33800 calculations. Is this number of calculations "too
high" for Access and they should be stored or not?

Next I will try to add more details to the "what" of the spreadsheets in an
effort to get to the 'point-in-time' question.

A) There are 65 franchise territories submitting weekly sales and royalty
reports
B) All report data is kept in an annual workbook, by franchise/territory
(worksheet) and week (row) and royalty calculations are based upon the weekly
data
C) Each franchisee has a 5 year contract specifying the royalty percentage
D) Each contract has different start-end times
E) As one contract ends it might be renewed but with a different royalty
percentage
F) If one contract defaults before the end of the term, a new franchisee may
'purchase' the territory, have a different royalty percentage, and a separate
franchise/territory worksheet

The db structure will include the royalty percentage for each
franchise/territory.

The question I guess I had was: 2) Do I have the report/query calculations
look up the royalty percentage value up each time or store the calculated
values?

After writing all of this out, it seems to me that this is not so much a
'point-in-time' kind of data (like the price of an item). It is more like a
rate change over time (like mileage reimbursment rates) and that I don't
store the calculated values. The lookup will be a 'cleaner' db structure and
the queries will just be 'harder' for me to figure out.

 

[Larry Daugherty]

1. Don't store the calculations. If the time to calculate for a
report seems long then look into speeding it.

A. Each sales and royalty report goes into a record. The
Franchise Identifier is an attribute of that record (Probably as a
Foreign Key).

B. All data must be kept in a single set of tables (entities) in
your Access database. There will be a separate table for each entity
type. Every entity of that type belongs in that one table. For
instance, the year doesn't get a separate table. The year or, other
expression of date, is an attribute of the record. Take the issue of
Contracts, for example:

tblContract
ContractID Autonumber (Primary Key)
FranchisorID Long Integer (Foreign Key = Primary Key
of tblFranchisor.
StartDate Date Begin date of the contract
EndDate Date End date of the contract
Royalty real Royalty enumerator
Note Text Notes about this contract

There will need to be table for Franchises in which each record
identifies a single franchise and has the royalty percentage for that
franchise/territory. ?additional to the percentage in the contract
percentage?

2. Look them up.

For queries on the contracts, the currently open contracts will be
those whose EndDate is less than tomorrow and whose StartDate is equal
to or greater than today.

The most important concept to master before investing much time and
effort into a design is that of Normalization.

Post back with questions.

HTH

 

[rpw]

Thank you again for your help. This answers the questions I had on this
aspect of the project I am working on.

As I come up against new stumbling blocks and have questions about how to
resolve them, should I add to this thread or start a new one?

 

[Larry Daugherty]

Start a new thread for each new issue. Avoid piggybacking a new issue
into a resolved issue. The thread should be about the issue noted in
the subject line.. Some people won't join am ongoing thread eve
though that's the whole idea.

Just noticed that I misstated the EndDate for current contracts. It
would be >= Today().

HTH