Table Relations - Many tables to One
I have been searching through the forums to look at possible solutions to a problem that I am having. I know logic tells me that I should be doing this in one table, but I'm trying to find a way to make this work in it's present for.
The reason for my resistance is that the 4 Supplier tables are potentially going to be changing regularly and at different times. It is possible that the whole table will change when the supplier releases their new pricelists. Also as they are differnet suppliers, it is highly likely that the format will be extremely different.
I have attached a link to a small Database of what I'm trying to acheive;
The idea is to have the Item Name and Item Price automatically populate when the Item Order is created. This means that the Main table will have to link to the indivdual supplier tables, Item Order --> Item_ID
The problem that this means is that the one table item will be linked to 4 tables, each with different references.
Is there a way that I can get the two fields in the CustomerInfo table to look a the corresponding Supplier table based on the Itme Order, being A#, B#, C# or D#?