I have a database that contains 13,772 records and each record contains 34 fields. The database is used for historical research purposes. As I am constantly changing the content in one or more fields I would like to be able to have a field that would show the date of any changes that are made to any of the fields in any given record. Additionally I would like to be able to run a query, lets say every six months and extract all the updated records. Any offers of advice on how to proceed would be very much appreciated. Thanks in advance.
Automatic date entry for field changes. (1 Viewer)
- Thread starter MikeD
- Start date
- Local time
- Yesterday, 19:02
- Joined
- Jan 23, 2006
- Messages
- 15,379
does that mean it is only 1 table?
I would add a field to the table (perhaps 2 fields)
LastChangeDate ---date of last change to record
LastChangeBy ----- name of person who made the change
Now if you want to get more involved.
You could have another table.
MyLogTable
LogId -----unique identifier of log records
TableId -----table where changed record exists
RecordId----Record with change
FieldName--Field beiing changed
FieldBefore--FieldValue Before the Change
FieldAfter----FieldValue After the Change
ChangedDate---Date of Change
ChangedBy------Person who made change
For more details and concepts see Allen Browne
http://allenbrowne.com/appaudit.html
- Local time
- Today, 00:02
- Joined
- Feb 19, 2013
- Messages
- 16,616
There are a number of ways, but none of them will work (except data macros) if you are working directly on the table - you need to be working via a form.
You will need an extra date field (called say TimeStamp and with a default of now()). If you just want to know when a record was created, that is all you need.
If you want to know when a record was changed, you will need a bit of code in the form beforeupdate event to update this field to the current date and time) -simply
me.timestamp=now()
Alternatively from 2010, you can investigate the use of data macros which are triggered when a record is inserted/updated etc.
Almost certainly not the way to go but assuming you are using 2007 or later, a relatively clumsy way of recording changes in a field is to make each field a memo field and setting its append only property to yes, it is not a feature I use and I don't believe you can query on it - I would google 'access memo append only' to see what you can find. Obviously, numbers and dates would be stored as text and booleans would have to be reinterpreted as text in some way which would have an effect on any queries you have
However if you want to know which field was changed then you need to investigate history tables - these are tables which record changes at this level - there are a number of techniques, which one is right for you depends on what you want to achieve - again - google 'history tables'
You will need an extra date field (called say TimeStamp and with a default of now()). If you just want to know when a record was created, that is all you need.
If you want to know when a record was changed, you will need a bit of code in the form beforeupdate event to update this field to the current date and time) -simply
me.timestamp=now()
Alternatively from 2010, you can investigate the use of data macros which are triggered when a record is inserted/updated etc.
Almost certainly not the way to go but assuming you are using 2007 or later, a relatively clumsy way of recording changes in a field is to make each field a memo field and setting its append only property to yes, it is not a feature I use and I don't believe you can query on it - I would google 'access memo append only' to see what you can find. Obviously, numbers and dates would be stored as text and booleans would have to be reinterpreted as text in some way which would have an effect on any queries you have
However if you want to know which field was changed then you need to investigate history tables - these are tables which record changes at this level - there are a number of techniques, which one is right for you depends on what you want to achieve - again - google 'history tables'
Hi jdraw
Many thanks for your very interesting observations on my particular inquiry. In answer to your question. I have just the one database, I enter all my data in form view, but also often open the form in datasheet view when I require to look at multiple records at a time. What I would like to do, is to include some way of recording the date when I either delete, edit or add data to any of the subject fields. I was very interested in your suggestion of having all the these changed records then entered into a new and separate table, this would serve the very purpose that I wanted, to be able to identify all the changed records from a given date. Of course this brings me back to the question, can this be done in Access 2007 and how simple a task would it be to set up. Again many thanks for replying with some very interesting possible options.
Many thanks for your very interesting observations on my particular inquiry. In answer to your question. I have just the one database, I enter all my data in form view, but also often open the form in datasheet view when I require to look at multiple records at a time. What I would like to do, is to include some way of recording the date when I either delete, edit or add data to any of the subject fields. I was very interested in your suggestion of having all the these changed records then entered into a new and separate table, this would serve the very purpose that I wanted, to be able to identify all the changed records from a given date. Of course this brings me back to the question, can this be done in Access 2007 and how simple a task would it be to set up. Again many thanks for replying with some very interesting possible options.
Hi CJ
Again can I thank you for taking the time to look at my question and provide me with some alternative suggestions to those of cjraw, I think that the last suggestion regarding the memo fields would be too complicated to consider, and I am also afraid that some of the fields that contain numeric characters and dates may also create problems. I am in fact using Access 2007 and do input all my subject matter in a form, and as I mentioned previously I tend to use the datasheet view merely for looking at multiple records on screen. However this brings me back to my original question of looking at the simplest way to attach a date to a field as and when I make any alterations to a record, I am hoping to find some interesting options in your suggestion of a google search of history tables. But I would have to say that the possibility mentioned by cjraw regarding being able to have a table into which all changed records were recorded into sounds a really good solution to the problem that I want to resolve, but the burning question remains is this possible, I hope that google may come up with some options, may I thank again you for the time you have taken to offer me some very interesting options to my inquiry.
Again can I thank you for taking the time to look at my question and provide me with some alternative suggestions to those of cjraw, I think that the last suggestion regarding the memo fields would be too complicated to consider, and I am also afraid that some of the fields that contain numeric characters and dates may also create problems. I am in fact using Access 2007 and do input all my subject matter in a form, and as I mentioned previously I tend to use the datasheet view merely for looking at multiple records on screen. However this brings me back to my original question of looking at the simplest way to attach a date to a field as and when I make any alterations to a record, I am hoping to find some interesting options in your suggestion of a google search of history tables. But I would have to say that the possibility mentioned by cjraw regarding being able to have a table into which all changed records were recorded into sounds a really good solution to the problem that I want to resolve, but the burning question remains is this possible, I hope that google may come up with some options, may I thank again you for the time you have taken to offer me some very interesting options to my inquiry.
- Local time
- Today, 00:02
- Joined
- Feb 19, 2013
- Messages
- 16,616
Thanks CJ
I have much to ponder now, and hope to make a start this weekend with some experimenting.
