I have two table, Parent Table and Child Table.
In parent table, I have the ParentID (Unique), Effective date and Rental.
In Child table, I have the RefParentID (ref), ChildID (Unique), Effective date and Rental.
Parent has the original Effective date and Rental.
Now I would update the rental for Parent Table base on the effective date and rental in Child Table.
For example, parent table has the information below:
ParentID - PID0001, Effective date - 01/05/2021, Rental - 1000
and child table has the information below:
RefParentID - PID0001, ChildID - CID0001, Effective date - 01/06/2021, Rental - 2000
RefParentID - PID0001, ChildID - CID0002, Effective date - 01/07/2021, Rental - 3000
Now, I would need to update the fields (Rental) in parent table to 2000 when TODAY()= 01/06/2021.
How can I achieve this? I had searched around but could not find any solution.
Any helps are appreciated, thanks.