Protect Data after Lookup
I have a database set up to manage booth rentals at a trade show. I have the following tables: Exhibitor Data, Booth Data, Booth Rentals, and Exhibitor Archive Data. I enter the booth rental records via a portal in the Exhibitor Data layout. The exhibitor data layout calculates the number of booths rented and other items for each exhibitor.
I have it set so that when I enter a new Exhibit Year in the Exhibitor Data table, it creates a related record in the Archive table. The archive table has a few fields set to lookup fields from the Exhibitor Data table (no. of booths rented, # of credentials, banquet tickets, total paid). I want to keep this data as a historical record of what this exhibitor did in a past year.
My problem is that when I enter the new data in the Exhibitor Data table, it creates the new record but the lookup fields from the past year are now blank. How can I "freeze" the data when I'm ready to enter next year's information? Is lookup the wrong way to go about this?