Dave Hobson wrote:
I can do all the calculations in the “report records” table, keeping things cleaner (and quicker) in the data file, holding only the meaty data in the data table, with all the unstored calcs etc. in the temporary table, doing their stuff only when actually needed.
Unstored calcs "do their stuff" only when actually needed - regardless of which table they are in. If you only need them for the report, don't place them on any other layout.
I have a similar setup in my reporting file, having done this I have a little food for thought.
1. Will your reporting be multi-user?
a. How to keep users from stepping on each-other’s records?
b. Who imported what record?
2. Using imports brings along housekeeping.
a. When do you clean out the file? Old records from previous report runs.
b. Keeping the report file nimble and quick.
3. What about multi-use records? Volunteer checks to be willing to perform several tasks and you need a single report listing the tasks a who is willing to perform them.
All of these considerations can be dealt with by planning a variety of techniques. None-the-less they come up, so better deal with them sooner rather than later.
BTW, I like my reporting file. Using it for me approaches the reporting with less clutter.
Thanks, Michael and Stephen, for your pointers.
Tim - thanks a lot for your several cents' worth! I had thought of some of these, but not all, so I appreciate your input. I wasn't sure, though, what you meant in your point about multi-use records - could you clarify?
Where this came up for me was in the following scenario:
I have several reports based on sales by territory. Occasionally we have a sale which is split into two territories, i.e. the commission will be split/shared by two different salespeople. In the sales order table there is only one record of the sale, but two territories selected. So this record needs to occur two places in the report once for each territory. I have reporting fields in the order which hold the territory data (total / territory count). When the records are imported into the reporting table, I read the count field and create the needed number of records in my reporting table. So, 1 sale with 2 territories ends up being 2 records( 1 for each territory) in the reporting table. Multi-use, same sale used in more than one place in the reporting.
So this process happens along with the user/account specific processing when records go in.
Hope this helps,
I see, that sounds really useful. Thanks for sharing this, and I can already see how it'll help in my solution.