Can anyone help me identify why I am receiving these duplicates? I am trying to look at Serial Numbers with a status of INVENTORY by Site/Plant, but also look at the transfer order associated with them. Currently as shown in the attachment of the results I am getting two different transfer order numbers for the same serial number. Its pulling a transfer order number for the same part but different serial number that isn’t associated with the serial number. Any ideas?
Follow your joins all of the way around, work the problem manually. You will run into data somewhere where your join doesn’t eliminate the duplicates.
Brandon,
What do you mean by follow them all of the way around? Do you mean experiment with different types of joins on all of my joins? Should I attempt to join the tables differently?
Get a sample of the data, drop each of the filtered table into excel or something so that you can see it. For example use the tables that you have show previously. Than with a pen or something, go through the work of doing the join yourself, and don’t just stop at the first one that is a match. Do it like a computer would, looking at each record. This will help you understands how the joins are working.
You have the serial numbers in the Serial number table, and the TFOrderDtl table, but they aren’t joined together. They go through the part table, and part bin. The part table doesn’t have serial number in it, so it can’t make that match.
Imagine 3 people at stations where they are passing a number from the first to the 3rd trough a screen and keyboard.
person one has number 123456, he types it into his keyboard which shows on the screen for person 2
Now person 2 has a broken keyboard with the numbers 1,2,3,5,6,7,8,9,0. (there’s no 4). But it has a space, so he sends that instead. So if he were to pass that number on he would only be able to send 123_56, missing the 4.
So person 3 gets 123_56, and if you are getting records that match all of those number, since the 4 is missing, you would get all records irregardless of what’s in spot 4. (or however this fictional system is designed to work)
Not the cleanest analogy, but you should understand what I am saying. Basically, if you join through a table with missing information, you are going to get duplicates.
If you walk through the exercise manually, you’ll understand better what the computer is doing.
1 Like
Brandon,
Thank you for the explanation, the analogy actually works really well.
I will try and make the joins work better to eliminate the duplicates with the excercise.