Creating new record when record is edited

[accessbro]

I'm working on a project that has project start / end dates which can change. A report is created based on this data. The customer wants me to save all changes as a new record and disregard the older record when the report is created. I was thinking that I would add a (yes/no) column that marks the new record as the most recent (yes) and marks older records as (no). Then I would modify the query for the report to only include records with 'yes' set to true. I was also thinking that I should create a second table that has a foreign key for the original record from the main table. The main table would have the most recently edited record while the second table would have the previous records. Which method should I use? Are there any other methods that are better / more efficient?

[June7]

I would not use separate tables. A yes/no field is one approach.

Maybe should look at audit log, review: http://allenbrowne.com/AppAudit.html

[accessbro]

That link you posted looks like it uses separate tables.

[June7]

Sorry, yes it does. It's been a while since I looked at that procedure and forgot that it actually duplicates the record with additional fields for date/time/user info. Some audit logs take a slightly different approach but still use a second table. I retract my original statement. Does look like another table is necessary for an audit log, which is really what you described in your original post.