Can I update a Table whilst keeping existing....

[Alisdair]

Data.

I have one table which holds the answers to a medical questionaire. 300 questions to be exact. This table has a Primary Key which is just an autonumber.

The problem I have, is that the questionaire results will be updated from time to time. Not the whole questionaire but maybe a couple of fields. Is there a way that the user can go back to the questionaire make the changes needed and then save, but at the same time still keep the original results in the table, but assign a new autonumber? The reason is that they do not want to fill out the whole questionaire again just to make a small change, but I need to keep all the results going back in history!!!!!

Sorry if I havent explained this perfectly. I have looked at the appen query and the update query but not sure if this would be correct procedure.

Thanks in advance. You dont have to tell me exactly how to do it but a hint in the right direction would be nice.

 

[tehNellie]

I'd suggest taking a look at the following article which describes a basic "Audit Trail" database that could be modified relatively simply to do what you want and would be much better practice than keeping multiple versions of the same data in your tables.

Basically what it does is store the "current" record in your questionaire table and track when a user changes data and what data was changed in a separate table. It's not quite a copy and paste job to do what you want to do, but it does contain all the basic information you'd need to modify it to do what you want.

It's been linked from here previously, but I couldn't find the original post.

http://articles.techrepublic.com.com/5100-10877-6166807.html

 

[Alisdair]

Thanks for that, I have just had a look at the link and it is very helpful, although I dont think it will serve my purpose initially.

I will try and word my issue a bit better in a short while.

Thanks again for the speedy response.

 

[Alisdair]

OK so I have thought long and hard and this is probably a better explanation for my requirments.

I have a form with questions on it, once it is filled out, it is then all saved to a table.
Later i want to be able to update the answers to only one of the questions. So i edit and press save. Now this would overwrite the original in the table.
What I want to do is save it but not overwrite it, I would like it to be saved as a new record. This process would eliminate the user from having to retype the whole questionaire again. I would then be able to run reports in what their previous answers where and compare them to the new ones. The only problem I see is that the PK would not assign a new number. Instead it would just overwrite what I want to keep...

So how do you edit a question that is already stored, but save it with a new autonumber...

 

[tehNellie]

You'd INSERT a new record with the new data rather than UPDATE the old one.

I guess you'd also have to add a "modified" yes/no flag or a field to store the previous KeyID to figure out what is a modified record and what isn't, but I honestly wouldn't recommed that you do it that way. If nothing else you are duplicating all the information on that questionaire except the new field. Plus, every time the user changes data you're just wasting space and at some point, especially if they can change more than one field in their questionaire, you're going to need to figure out what data has changed from one record to the next.

The "audit log" solution above would appear to do everything that you want except create a new autonumber field which, for the life of me, I cannot see is important.

 

[Alisdair]

tehNellie, you have alot more experience than I do I am sure, so I am going to take your advice and look into the "audit log" more. Really appreciate it mate, thanks

 

[tehNellie]

Alisdair, Obviously I can only go on the information that you are giving me, but I really cannot see a reason to hold your "change log" information in the same table as your live data, nor duplicating all the information that hasn't changed when someone edits their entry.

While we're only talking about a couple of hundred records right now I'll chuck one of my databases up as an example. Someone did exactly what you proposed to several of the tables in it. We hold "live" records and "old" records in the same table. Everytime someone modifies an entry we set a "modified" flag to 'yes' then create a brand new entry with all the previous data, plus the new information, in the table which might only be one column out of the 50 or so in the table. I have around 20,000 live entries that I need to use day to day and 45,000 entries that are modified data which I'm not interested in at all, except for twice a year when the auditors come. So over 2/3rds of my data in this table we don't do anything with and has to be filtered out. That represents quite a big performance hit on my database and means I'm storing far more information than I have to.

In the linked solution all you hold in your main table is the questionaire response as it exists now. Linked to that is you audit log which contains the ID of the questionaire, what the field was that was altered and it's "before" and "after" value which is all the information you are really interested in. Between those two tables, it is relatively simple to build a query or number of queries, especially using VBA, that will show you a complete change history for a given questionaire without having to duplicate all the information or bog down your "live" table with lots and lots of "archive" records that you might not actually be that interested in on a day to day basis.

 

[Alisdair]

This will probably work best for me then. I am going to work on it tonight and see if I can make it work!!!! Thanks again mate