We're a manufacturing company and last year we switched from an older ERP software package to a newer one. I've developed an app with dashboards for the sales team, but it's currently only working with the data from the new system. I'd like to import the data from the old system, just by concatenating tables and loading 'AS'.
This works fine for most of the data, but the issue is that the system for assigning customer numbers is completely different between old and the new, so I can't just concatenate. I put together a sort of 'translation table' to link the two, but I want to make sure my approach was sound. What I have is this (With made-up numbers, of course):
Customer Number - Old System
Customer Number - New System
As you can see, some customers are new since the adoption of the new system, others are present in both, and some only exist in the old system (Should they become customers again in the future, I'm not sure how we'll handle it, but that's another question).
I'm looking for advice on how to use this table to link the customer tables from both databases. I know the null values will cause problems, but I'm not sure how to handle them in the cases where each entry only exists in one database.
Apologies if I haven't articulated the problem well enough; I suspect it's a case of lacking the vocabulary on some level since I'm relatively new to all this. Thanks so much for any help you might offer.