Hi,
A few years back I created an Access database for our stock ordering system. One of the tables was a table of customers called tblCustomers with columns such as id (autonumber & primary key), name, address1, address2.... Two tables had many-to-one relationships with this table: one contains every item ordered and a field like customerID which allows us see which customer the item was ordered for the other table contains sales orders/delivery dockets which again have a reference to the customerID showing who the delivery went to. There are 400 customers records, 3000 sales orders/delivery dockets and 11000 ordered item records at this stage.
I later created another database for something else which also has a tblCustomers table containing customer info but the address and contact info are kept in additional many-to-one linked tables (like tblCustomerSites and tblCustomerContacts). The customer records in these tables are properly filled out and in a good format for us. There are about 150 contacts customer contacts in here. I've now moved these tables into a shared MDB on the server so other databases can use and update them. The stock ordering system is the only database now acting off its own list of customers and it's now causing us problems as there is info in the new customer tables (eg. who their contract manager is, if they're still active etc etc.).
So what I now have to do is get the stock database working off the same customer contacts table but I'd like to here from experts here who may have done something similar is there an easy way to do this? There are a few problems I can see:
1. Customer "Acme Ltd." in my stock ordering system has an id of 132 but has an id of 67 in my new shared customer contacts database.
2. Customer names have been entered by users over time so they definitely don't match one for one; we could have "Acme Ltd." in one database but "Acme Limited" in the other.
3. The stock ordering system is a good bit older then the new database, so there are customers in there that don't exist in the new database at all but need to so historical data is maintained.
Does anybody have any good suggestions? If at all possible I'd like to update the froeign key records in the sales order/deliveries and ordered items table in the old database rather then adding extra id columns to the new database with something like "oldIDReference". Obviously there will be quite a bit manual effort involved in this which is fine, but I'd hate to find a button in Access in a months time that would change this from a 5 day job to a 3 hour job.
Any and all help appreciated!
Start Free Trial
