Let's say that I have a customer's table (SalesByCustomer) with a list of purchases that the customer had bought.
Let's say that I only want to work with aggregate sales for each customer, but I still want to preserve the individual purchase lists for reference purposes.
How then would I create a new table, AggregateSalesByCustomer that does the following:
1) Group Customers together and sum up their purchases and link it to their customer ID
2) Populate a new table with the aggregate/ID fields
3) Carry-over fields from the original SalesByCustomer table such as their contact number, email, etc.
4) Automatically update the new table (AggregateSalesByCustomer) depending on new data flowing into the base table - SalesByCustomer?
Is this possible? Is there a simpler way to do this? How would I go about implementing such a feature?