Hello,
First of all i do not know if there is a solution to this problem but i will give it a shot. On my project i am combining a Sales Information table and a Gross margin Table. The Sales information table contains sales order, customer name, number, city and state. The Gross margin table contains sales figures. What links the two tables is the Sales Order number. My problem is that the company reuses Sales Order numbers with customers. For example customer X buys 5 widgets on order 0001 then a week later buys 5 more widgets and it gets put on order 0001 with a different ship date. One order the customer placed two orders for the same amount on the same day. When i try an outerjoin it only picks up one of the orders on that day and when i do an inner i have record expansion because of the reuse of sales order numbers. If this were a small table i could manually fix it but it is over 40,000 records in each table. I dont knwo if it is possible to solve this due to the bad business practice of reusing sales order numbers or not but i would appriciate any solution or advice. Sorry for rambling
First of all i do not know if there is a solution to this problem but i will give it a shot. On my project i am combining a Sales Information table and a Gross margin Table. The Sales information table contains sales order, customer name, number, city and state. The Gross margin table contains sales figures. What links the two tables is the Sales Order number. My problem is that the company reuses Sales Order numbers with customers. For example customer X buys 5 widgets on order 0001 then a week later buys 5 more widgets and it gets put on order 0001 with a different ship date. One order the customer placed two orders for the same amount on the same day. When i try an outerjoin it only picks up one of the orders on that day and when i do an inner i have record expansion because of the reuse of sales order numbers. If this were a small table i could manually fix it but it is over 40,000 records in each table. I dont knwo if it is possible to solve this due to the bad business practice of reusing sales order numbers or not but i would appriciate any solution or advice. Sorry for rambling