Updated Fields in a table

[Fadhl]

I have an access table with multiple fields that get updated every now and then. I would like to have a table in the same database that contain the records with specific fields that were updated within a specified date range.
my table gets its data from a sql-based program automatically. this table contains product information: PLU, DESCRIPTION,PRICE, DATE MODIFIED. The table I am trying to have is one that would only have rows of items whose price was changed within a specified date. I would imagine that it is possible to have such a table that would receive these updated records every time any of their field are changed, I just don't have the knowledge to structure and design such a table BUT I am sure that this forum would have plenty of great minds capable of doing such a task. Please help

[June7]

How is the SQL program updating specified records in table - by code in the SQL program or in Access?

Maybe this will help http://allenbrowne.com/appaudit.html

[Fadhl]

My actual database is in SQL. When I change for example a price of an item I do it manually in SQL by selecting an item from the inventory manager of the SQL-based program and then I simply put the new price and click ok to apply the change.
What I really want is to be able to have a table in access linked to the SQL table containing the inventory items.
I made a table in access and linked it the SQL table and it does automatically pick up all the changes as they occur in SQL. The SQL table has among its field one called modified date which stores the date and time of the change to that record regardless of the type of change that took place. In other words, if an inventory item is sold, SQL records that as a change since that item (record) did in fact experience a change which was a sale and the same happens when the price is reduced or increased for that item.
I was wondering if it is possible to have a query in access or any other procedure in access to monitor only the changes in the PRICE field of a an item record and store the new value automatically in a separate table in the same access database. One idea is to have the linked table always make a copy of itself that is but not linked to SQL and then have a procedure to compare the new values of the PRICE field of this copy table to the newly updated fields in the linked table. I don't know how to do this But would appreciate your help.

[June7]

Yes, I expect could be done. Table in Access would also store the change date. You want a history of all prices saved in the Access table? Compare latest change date in Access table for each product with the change date in SQL table and if they don't match then a change took place and save records to Access. I expect this can be done with a sequence of queries or totally by VBA.

First query would be an aggregate (GROUP BY) that would show the Max change date for each product in the Access table:
SELECT ProductID, Max(ChangeDate) AS MaxDate FROM tablename GROUP BY ProductID;

Use that query in a find unmatched query - there is a wizard for that query.

Records in SQL table that don't match the Access table can be data source for INSERT query. Google: Access query append unmatched.