Particular field value changes every month, but want to keep historical information.Setting this up.

[McArthurGDM]

Hi,

I have 2 tables.

Table A contains a list of Projects that evolve over time. Example:

Table A
ID Project Name Comment Comment Date
__________________________________________________ ________
1 Name 1 Comment 1.1 12/22/13
2 Name 2 Comment 2.1 12/20/13
3 Name 3 Comment 3.1 12/02/13


Now, let's say that Table A changes over time - just with the Comment portion. Example:


Table A
ID Project Name Comment Comment Date
__________________________________________________ ________
1 Name 1 Comment 1.2 01/20/14
2 Name 2 Comment 2.2 02/14/14
3 Name 3 Comment 3.2 01/02/14




Obviously, I would use an Update query to override the previous information.

But let's say that I want to preserve the previous information for historical use? How would I set this up?

Thanks

[JoeM]

Obviously, I would use an Update query to override the previous information.

But let's say that I want to preserve the previous information for historical use? How would I set this up?

If you want to retain the original data for historical use, I think I would go about this a different way. I would use an Append Query to add a new record, but keep the original. What you might want to do is add another field to your table that indicates whether a record is active or not. So, when you have an update, you mark the original record as inactive and create a new one.

Or you could create a "historical" table, and create a copy of the original in this "historical" table and update the one in the other table through an Update Query. Personally, I like keeping everything in one table. It makes searches easier.

[NTC]

it seems to me that there is a terminology issue; you say you have a table - and if you do then there is no limit to the number of rows and you can just add rows of data and be able to see everything ever entered.... but your description implies that things are being over written which means you actually today only have a set of fields within a table dedicated to this information. So what you need to do is change the design to be its own table - inset as a sub form.