Create table or use queries? (1 Viewer)

[Malcy]

I have a dilemma. I need my app to print out legally binding property schedules for insurance purposes. A property can have any number of schedules created during a year according to how many changes are made.
My instinct is to use a set of nested queries to generate the reports and then save them as snapshots should they need to be referred to later (they will!!).
However I have just been wondering if I should create a new table that gets populated with the full data for each property schedule when one is printed so that there is a definitive and tangible record for each schedule.
The latter seems like not good practice within Access as I know it but I have this niggling hunch that it might be the right approach for this app. On the lazier note it would also make re-creation or subsequent investigation very much easier than having a whole sequence of horribly dynamic queries!
Does anyone have anything similar (sure someone does) and what did they do?
Thanks for any input

 

[davea300]

Yes you can create a table that gets popluated when you print a report. Not sure about anyone else's opinion on best practise for this but I have used a method to do this before.

I placed some code in the Format event of the Report header section to copy certain fields from a form (which produces the report) into a table so that the letter could be reproduced.

something like this:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Yourtablename")
rst.AddNew
rst!ReportName = "Your report name"
rst!PrintDate = Now
rst!ApplicationNumber = [Reports]![ReportName].[IDNumber]

rst!fieldname1 = [Forms]![YourFormName]![YourFieldName1]
rst!fieldname2 = [Forms]![YourFormName]![YourFieldName2]
rst.Update

End Sub


Basically you have a form that contains the fields that you want to use in your report. When you click a button on the form it print previews the report and when you print the report the code above copies the fields you specified into a table. You can then use that table to reprint any report.

HTH

 

[Malcy]

Thanks davea300
Not quite what I had in mind but the principle is the same, and yes it would achieve the objective.
Yours is a nice one where people populate from a report but I generally use a form, so effectively the same process.
I worry slightly that storing this data seperately it will effectively de-normalise the database so far as I can see. However I will need people to be able to recreate reports precisely as was and cannot see a better way to do it. So the impact on normalisation may be so slight as to render it an unnecessary worry.

 

[FoFa]

In the past, more for simplicity than anything, I have created a WORK table, and delete and populate it via queries and run certain reports against it, usually via a macro or a bunch DOCMD's. I like to use that approach when a few or more changes are required to the data, or denormalizing the data for a specific reporting requirement. Easy is not always a bad thing.

 

[checker]

Creating a table with the actual document data is a good idea, you can then also assign & store document numbers (which might be an appropriate thing to do for legal documents). Also include Print dates and if you wanted you could then create another table that would allow you track mailing dates, signatures, and what not for each legal document you have printed.

I've worked on something similar for invoices and because the invoices also had to be a snapshot of current data - an archive table was great.

Also, you really want to create a history document archive so that you can re-print anything when needed. When the table/database gets too big for you, then save a copy of the archive table into a separate storage database, clear out the original one, compact & repair and you are all set for another few months.