Check for a field value being updated/changed SQL (Access)

Question

Hi there,

This is certainly a long shot, and is by no means vital to my development requirements, so if there's not a readily available solution the please note; I won't be too upset ;)

I was wondering if there was a way to see if a field value had been changed or updated within a date range in Access.

For example, I have a status field in lets say table1 that may read "active" or "inactive" (simply via validation, no related tables for this field), I would like to see how many records changed from "inactive" to "active" within 30 days.

I have found a solution for timestamping a form update, and if worst comes to worse, I can just amend this to apply to a field, but I would rather be able to search for the value changes than the date the field was last changed.

Again, if this strikes anyone as impossible, then please don't worry yourself too much.

Regards,

Andy

Answer 1

You need to have a change history.

Separate table which stores the key of the row as foreign key, the status and the timestamp. every change inserts new row to the table.
Depending on the technology you are using, the easiest way is to use trigger. The trigger can check if the the field is changed odl.status <> new.status and to insert new row in the history table.

If you do not like to keep history, then only one field in the same table can do the job. The field can be datetime, and also the trigger can update it when the status is changed.

Timestamp will not do the job because if some other field is changed this field will be changed.

So in this case also the trigger can do the job.

But also depending of the type of the client, the client can detect if the field is changed and update the datetime field.

Answer 2

Thank you, I shall give this a bash!

Andy.