Table Join Problem

[BenTitus]

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

 

[sillysod]

are the order numbers set for specific orders ?

eg 0002 is always an order for 5 widgets and 10 thingybobs,
0004 is always 10 thingybobs and 20 wotsits

 

[TonyJollans]

Hi BenTitus,

What are you looking for as a fix? Do you just want to remove the duplicates (as a one-off exercise) or do you want to redesign your system to cope with the duplicates?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[gusbrunston]

[tt]
I think there should be a meeting of all department heads about changing to unique order numbers.

You can tell 'em Gus said so.[/tt]

[tt]Gus Brunston - Access2000(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[BenTitus]

Hello,
No the orders arent set to specific amounts just to specific customers. What i am trying to do is redesign it to deal with duplicates and it has except for orders that have two updates on the same day. I have repeatedly told them that for better data quality and even just better business practice to have a unique identifier to track sales but i am a lowly intern so my word does not have much say. Any help i would appriciate.
Thanks in advance

 

[BenTitus]

Hello once again
I solved my problem. Surprising what you can do when you step away from a project for a while. What i did was make a create table query where i include the sales order and customer information and then made the query distinct so only one sales order would show with the proper information. From that made table i did a right join to the gross margin table so each sales order had the customer information assigned and incase there were inconsistancy which, is very possible with the business practices, i kept other financial information in the table but without customer data.

Thanks for your help