Retaining Final Data for Historical Reporting

[rjgriffin46]

I am now developing a database for use by our Community Foodbank of New Jersey kitchen to track preparation and delivery of meals to various sites in our Kids Feeding program. Some meals are delivered once daily and some a week's worth of meals (5) one day per week.

For each delivery there will be a report that includes a detailed list of the menus and the items in those menus. The report will have 1 page for each site receiving food and a total page summarizing the amount of each item prepared and delivered to all the sites. These reports will be run daily and may be run multiple times as changes are made (eg., a site closes on short notice due to an emergency so their delivery previously included on the report has to be cancelled and the report re-run). The report needs to be printed as it is used by kitchen staff to pull items from inventory and by the delivery drivers to obtain a delivery receipt signature. A sample of the report currently in a spreadsheet is attached KIDS CAFE 2017-18 Hot for Mon 6-4-18.zip. Print previewing the 1st tab (Site Menu) will give you an idea of the report. The totals (used for inventory pull) for the date appear on the 1st page and then each site's data is on the right as you page through the report.

At the end of each month there will be additional reporting primarily to summarize the number of meals prepared and delivered to each site and overall program totals for the month. For integrity purposes in this historical reporting it will be important that none of the underlying data change (unless of course there was an error that needs to be corrected). I am wondering what process or method would make the most sense to basically "freeze" the reported data so that no unintended changes are made at a later time?

One approach I've entertained is using an append query to add the data to a "report data" table each time the report is run, storing the date and time run in a field in the table. Then for historical reporting purposes this table would be the record source and any query against it would use the latest time stamp for any/each delivery date. If we didn't feel the need to keep a record of each iteration of the report for the same delivery date a delete query could be used to remove all but the last set of records.

As always, I'd appreciate any thoughts, guidance and information referrals on this matter.

Thanks

[ranman256]

Id just keep it. It cant be filling much space, and your historical data is right there.
you can always lock data if the date < m/d/yy (or similar)

but at the end of the year, I always make a final backup MyDb-Yr2017.accdb
this is always read only.

[rjgriffin46]

Id just keep it. It cant be filling much space, and your historical data is right there.
you can always lock data if the date < m/d/yy (or similar)

but at the end of the year, I always make a final backup MyDb-Yr2017.accdb
this is always read only.

Thanks for your reply. A couple of questions:

1. When you say "Id just keep it." are you referring to the whole database or if not then what specifically?
2. Please explain "you can always lock data if the date < m/d/yy (or similar)". What does this mean and how is it done.
3. What do you think about the "Report Data Table" approach described in my original post?

Thanks

[June7]

1. Keep all data

2. Apply filter criteria

3. I would NOT do separate table, apply filter criteria to recreate a report