We do a 5 year comparsion chart here where I work from data entered into a line items table. We are open 6 days a week, have from 500 to 1000 customers a day with a minimum of 4 line item records for each customer. That adds up to a very large number of records being summarized.
My solution to producing that report in a timely fashion, was to not produce the report directly from the line items table. Instead, every evening, a script loops through the day's records and summarizes the data into what is usually less than a dozen records, data that is then stored in a "summary" table. Each record represents one type of line item purchased on a given date, with fields for quantity and price. By generating the report from this much smaller number of total records, I get a report that pops up very quickly even though I am looking at 5 years of monthly totals and averages.
What would be a good script trigger for this? I was thinking of getting the script to check a box when it is done and I could then run the script every time the database is opened to calculate the unchecked records.
What should be done if I make a miscalculation in one of the sales? E.g. if a sale had a shipping cost of $5 and then I find out that the shipping cost only $3.