I have two different systems, from which I am pulling order lines information (item, quantity, order number, customer code, date etc). Being the systems different, the structures of these two tables are also different.
I need to combine these tables to make one single backorder report; this report is an historical log, where each week the new backorder data is appended. But I also need to keep the originals order lines table to make some charts and reports ouf of that.
Further, the backorder table and the two order tables need to be somehow linked to each other.
For example, I would like to select a customer (in the order line table) and see the BO for just that customer.
The only field which is quite consistent within the two tables is the item field.
But, optimally, the BO table should be linked through the line field, as the BO actually happens on the line, not on the part.
If I link through the item field, it happens that: if I select item xyz, I see customers a, b, c, d are possible selections. Problem is, these are the customers which ever purchased item xyz and perhaps only customer b has a backorder.
This because the link is the part xyz and not the order line.
What is actually done in such situations, when two different sources tables needs to be kept and at the same time combined in one report?
"port" all the order lines fields (many) into the BO table?
normalise date between the two order lines tables, so to recreate a single order lines table?
in both cases, it seems to me a lot of work.
thank you so much!