The connected table, contains order, shipment, Payment, and Line Item Data, all of which may not be unique. The data would be better stored as the following 4 separate tables:
- An Order with 2 line Items would be held in 2 rows, where the Order Info is the Same and only the Item Detail is different, if the item, has not been shipped nor paid for these Col. would be blank or "N/A"
- As each item is paid or shipped that specific info is added to the existing Row of information
- If there is a second Payment, that would create a new Row with a duplicate all of the other information
- The Data we receive contains 90 days of changing order history
- The Orders change as they are shipped, Paid for and/or Cancelled.
- There are no Order Line #'s defined, so these would need to be generated, (I was thinking of a way to count all the order Line Items, that have with same or smaller Record ID, with some filters to select the single order Line Items.) So the connected table would need to hold all past orders as to not have these # change.
- Same Deal for the Payments and Shipments