Tracking Changes in Field Values

[RogerCooper]

I sometimes need to use Access to track changes in the values of external database whose structure I cannot change. For example, the external database has a keyed table of Products and I need to create a log of changes in the value of the Price field. I only need to know the date of the change.

I have created solutions for this situation, but I would like to hear how others handle this.

 

[Ranman256]

In the form, the ON LOAD event collects the 'before' values.
Then when user clicks 'save', code compares before/after Val's then posts the ones that changed to the tLog table.

 

[plog]

I only need to know the date of the change.

That doesn't sound right. Sounds like you need to know the product, price and date of change. So create that table:

PriceHistory
ProductID, number, holds the id number of the product working with
Price, number, holds price of product
PriceDate, date, holds first date this price was encountered

Let's call the external source table ExternalSource. The first run is a straight APPEND query to just copy ExtenralSource data to the new table. After that, to find new prices you would set up a few queries:

You would set up a 'PriceHistory_Current' query that shows what the most recent price (CurrentPrice) is in your PriceHistory table. Next you would create a new query using ExternalSource and PriceHistory_Current, make a LEFT JOIN from ProductID in ExternalSource to ProductID in PriceHistory_Current and Price in ExternalSource to CurrentPrice in PriceHistory_Current. So you will be showing all records in ExternalSource and just those that match in PriceHistory_Current.

Bring down ProductID, Price and a date field from ExternalSource and ProductID from PriceHistory_Current. Under ProductID.PriceHistory_Current put 'Is Null' in the criteria. This will identify all 'new' prices/products in ExternalSource. Turn the query into an APPEND and that will move the correct data into PriceHistory. Save and run this query to update PriceHistory.

 

[RogerCooper]

That doesn't sound right. Sounds like you need to know the product, price and date of change. So create that table:

Bring down ProductID, Price and a date field from ExternalSource and ProductID from PriceHistory_Current. Under ProductID.PriceHistory_Current put 'Is Null' in the criteria. This will identify all 'new' prices/products in ExternalSource. Turn the query into an APPEND and that will move the correct data into PriceHistory. Save and run this query to update PriceHistory.

There is no price date field in the external table only the price field itself. So there is no simple way of identifying a new price.

 

[plog]

My method still works. On the APPEND you just use todays date for PriceDate

 

[The_Doc_Man]

RogerCooper, you say this field is in an external database over which you have no particular control. If you have no control, that means you can't modify that DB to include auditing code and probably can't record transactions to that field to remember the date of the transaction.

Therefore, your problem is that you must sample the field on a regular basis. If the field has changed, the ONLY thing you can know is that it changed between the prior sample and the sample that saw the change. This comes down to sampling theory. So... how often does this field change and how accurate does your date-of-change have to be? Sampling theory says if you need the date accurate to the day then you must test not less than twice a day, but if the price changes more often than once per day then you need to schedule sampling at half the estimated lifetime of the price. Or approximately that long, anyway.

 

[RogerCooper]

RogerCooper, you say this field is in an external database over which you have no particular control. If you have no control, that means you can't modify that DB to include auditing code and probably can't record transactions to that field to remember the date of the transaction.

Prices were just an example, as I need to do this in different fields in different tables. In any case, I don't need to track changes to more detail that by the day. I can't modify the table to including auditing code.

 

[jdraw]

RogerCooper,

Can you tell us in simple terms
- why you want to do this or
- what your database will be used for?

I'm just curious.

There may be options to do whatever you need, but we really need more details on your needs.