Lock child field when parent field is changed on condition (1 Viewer)

[chacal33]

SOLVED: Lock child field when parent field is changed on condition

I am new to this forum and so far I have managed to find answers to all my questions for my access project.
But I got stuck. I tried various options but they did not work... :banghead:

I have 2 tables:
T_Product
T_Transaction
where the ProductID is the link.

And 1 query:
Q_Transaction
from which i get the value of the transaction

When i change the buying price in T_Product, depending on the date i changed this price, i would like the price in Q_Transaction to update or not... Because, obviously, last year's transactions have not had their value changed.

I am sure it is a very common topic, but for some reasons, i just cannot work it out.

Thank you kindly for your POVs on this.

Matt

 

[CJ_London]

you cannot update a field in a query - only in the underlying table for that query. If the query is editable and you manually change a value - you are actually updating the underlying table,

From your description, you need a field in T_Transaction to store the price at the time the record was created - one of the few occasions where data is 'duplicated'

Alternatively your T_Products table (or more likely a separate table) contains a history of prices and when they were effective from so you query would look up the price based on the transaction date.

 

[jdraw]

please see my posts here and here

 

[chacal33]

Thank you so much guys, this is really helpful.

The links you posted jdraw are exactly what i was looking for

I will get cracking!