Automatically creating and updating new tables based on a record
As the name suggests, I would like to copy and consolidate records based on one field into new tables. Perhaps an example would help:
Let’s say I’m a store that sells apples, oranges, and bananas. Every time I sell a fruit, I record it in the Main Table by selecting the fruit in a dropdown menu with other purchase information like customer name, date and time of purchase, etc. The dropdown menu is multi-select since customers can buy more than one kind of fruit at a time.
In addition to all fruit purchase information in the Main Table, I would like to create three new tables: Apples, Oranges, and Bananas that only contain the purchase history of the one respective fruit. The idea is that each table will automatically copy over a record from the Main Table when I select a fruit.
Any ideas on how I might go about doing this? Thanks in advance!
