I've now completed the Access Expert series and my database is coming along nicely but I need your help with a little problem.

I organise holidays for groups. Part of this involves making hotel reservations. I store the information for each hotel in AccommodationT. The reservation details are in a separate table, ReservationT. Each year the cost of the rooms changes (usually they increase!) and details, including the DateUpdated are stored in AccCostsT.

I've created queries that work out the most recent prices for each hotel. I use this to calculate the total cost for each reservation, the oustanding balance, etc. It all works well so far.

My problem is that when I look back at earlier reservations, these values change if there has been a price increase after the reservation was made. I need a way to lock these values at the time of the reservation, or perhaps store them in another table, so that they are unaffected by future price changes.

Thank you.

---

Reply from Alex Hedley:

You either have to create another table which stores the price, Rich has shown this in one of the courses, you pick a dropdown of products and it copies the details from the ProductT to the OrderT so if the Product changes the Order doesn't.
The other option is to store the Prices in a table with a date, then when you add a new price, you add the date range, and your query then uses the date ordered to get the matching price given that date.