Access - how to derive a field upon entry of a record
- by jonos
I have an Access database for a media rental company which includes the following tables, among others;
LOAN:
customer_id (pk),
loan_datetimeLeant (pk),
loan_dateReturned (pk)
LOAN_ITEMS:
customer_id (pk),
loan_datetimeLeant (pk),
item_id (pk),
loanItem_cost
ITEM:
item_id (pk),
product_id,
item_availability
PRODUCT:
product_id (pk),
product_name,
product_type
MEDIA_COST:
product_type (pk),
product_cost
So basically the 'product type' (DVD, VHS etc) determines the cost. The 'product id' determines what movie, platform etc each item is.
My question is:
When creating a form for the Loan_Items table, how can I populate the loanItem_cost field (so it is stored) whenever a new Loan_Item record is added? I need to store it as the cost of the item (product_cost) may change over time and I'd like to record what the customer paid at the time the loan was made.
Thanks in advance
