I am trying to put together a customer base that pulls in information from multiple bases.
I have several archived sales data bases, so a base for 2020 sales data, 2021 sales data, etc.
Let’s say I have a customer “John Doe” that has multiple orders in each base. I’ve got a CUSTOMERS table in each base that summarizes number of orders, total revenue, etc.
Can I create a “Master” customer base so that when I sync from multiple sources it tries to find an existing record with that primary key before creating a new record? When I try it is creating a new record for “John Doe” for each source instead of placing each sources information into the existing record. So if I’m sycing three years of customer information, I have three John Doe records instead of all of that info in one record.
This sounds right to me as the data in each base’s Customers table is technically customer sales data for that year, and so it’s unique data that should exist in its own row
If you’d like it to just be the customer’s details such as name, address etc I think I would suggest creating another table for that and populating it as such. The current table you’ve got where you’ve synced the Customers tables from the other bases can then be renamed to Sales by Year and we’d link the two tables via the primary key