Making a report database

[UT227]

I'm planning out a report generator database. I need to make it so that I add names to the report. I need to keep a history of the names entered. A report might have multiple names. The names might be on different reports. Here's what I'm thinking on planning it out. On the tables: [tblReport], [tblNames], and a junction table between them [tblReportXNames]. I'm not sure how to go about adding names to the report. Because I need to keep history on each name, I was wondering if I had [tblNames] without duplicates. I would use an append query to append all of the information into the junction table, not just the primary/foreign keys. That way, I have history in the junction table that is searchable and all the information is not changed with updates. This is a new project and was hoping to get input.

[ranman256]

Normally, you name a report, it stays that way.
The Client report shows clients, why would you name it differently next week? (or are you talking report header captions?)

you can have a master tReport and its child table with all the various renames.
you would need a tLog table to track the reports and names and the like.

[orange]

Before determining some algorithm for renaming reports, I recommend you identify what Report(s) you need; what info would each contain; who are the possible users of each Report.

You may want to read up on Dynamic Reports, M$oft article

[UT227]

I guess I'm not being clear. I don't need to rename the report. As I enter names, the names may need to be used again in another report. If the person's information changes in the next report, I need to update the information. Now, If I update the current record, Then all records will change. I need the old records to keep the old information. I only need to keep the new information in the new report. That's the reason I'm thinking that having a names table ([tblNames]. When I make another report with the same person, to add the information, not the primary key/foreign key, into the report. That way, I'll keep all of the old information in the old report. I think that I could do this with the use of an append query. I was asking opinions of this or if there is a better way of accomplishing this.

[John_G]

I only need to keep the new information in the new report.

and

I'll keep all of the old information in the old report.

Reports do not contain data - they only display it. The data is contained in tables, and reports are derived from those tables or queries based on them. The data a report displays can be controlled by filters applied when the report is run; the same report can be used for different data sets.

Since you want to keep historical data (which is a very common requirement), you are going to need multiple records for people whose information changes, together with fields to indicate the period of time when each version was valid, e.g. Start_Date and End_Date. The current version will have no End_Date.

Now, if you want to generate a report showing the data as it was at some point in the past, you run the report with a filter to exclude those records where the date you are reporting does not fall between the Start and End dates.

So, to start, I suggest you look at your data table(s) to see how you can best organize your data to record its history.

[ArviLaanemets]

As it was said here before, reports display/print data stored in tables in a designed format. p.e. you have a table MyTable which has a field EntryDate. You design a report MyReport based on query "SELECT * FROM MyTable". Now you can anytime (today, tomorrow, after 10 year) run this report with condition "YEAR(MyDate) = 2017", and unless the year is current one, the same report is displayed/printed - on condition that data for this year in table MyTable IS NOT CHANGED (and you haven't changed the report design meanwhile)!

When keeping historical data unchanged is essential, you have to restrict users to change it in tables (p.e. data are locked on monthly basis).

Other way to keep historical data is an old one - print the report out, and keep it in archive room. Or a newer version of this - print it as an pdf-file, and keep this pdf in some Document Managing System (DMS).

[UT227]

I do apologize for using the wrong word. I meant record, not report. I'm still planning my database. It's not developed yet. Yes, I need to keep history of each name entry.