Forum Discussion
Merged queries resulting in duplicates
I am trying to merge two queries. The first query has Agents Credits given data. This query Identifies how many credits an agent gave each month they were employed. This query includes 4000 different agents and most of these agents have multiple entries because they have worked for multiple months. The second query Identifies how many customers each agent saw. This query includes 4900 different agents and most of these agents have multiple entries because the agent has worked multiple months. My objective is to have one complete data set that includes credits given for all agents and customers seen for all agents. I choose the left outer join which matched 20,792 out of the first 27,967 rows. My results came back with an extraordinary amount of duplicates . For instance ID # 1 has only three entrees in each individual table but after both tables are merged it now has 9 entrees. ID# 4 Has 8 entries in each table but 66 entries in the merged table. What am I doing wrong? Below is an example of the two individual tables and the merged table
| Agent Credits | |||||
| Month | Retailor | Site | Agent ID # | Credits | Credit Amount |
| July | Cambells | Clara | 1 | 20 | $329.00 |
| August | Cambells | Clara | 1 | 18 | $245.06 |
| June | Cambells | Clara | 1 | 16 | $260.96 |
| May | Cambells | Clara | 4 | 8 | 31.51 |
| July | Cambells | Clara | 4 | 17 | 107.45 |
| June | Cambells | Clara | 4 | 22 | 164.01 |
| September | Cambells | Clara | 4 | 19 | 198.34 |
| November | Cambells | Clara | 4 | 9 | 54.5 |
| December | Cambells | Clara | 4 | 7 | 85 |
| August | Cambells | Clara | 4 | 17 | 128.3 |
| October | Cambells | Clara | 4 | 21 | 190.48 |
| Agent Customer handled | ||||
| Agent ID# | Retailor | Site | Month | Customer Count |
| 1 | Cambells | Clara | June | 518 |
| 1 | Cambells | Clara | July | 594 |
| 1 | Cambells | Clara | August | 468 |
| 4 | Cambells | Clara | May | 543 |
| 4 | Cambells | Clara | June | 957 |
| 4 | Cambells | Clara | July | 993 |
| 4 | Cambells | Clara | August | 838 |
| 4 | Cambells | Clara | September | 656 |
| 4 | Cambells | Clara | October | 914 |
| 4 | Cambells | Clara | November | 623 |
| 4 | Cambells | Clara | December | 385 |
| merged table | ||||||||||
| Month | Retailer | Site | Agent ID# | Credits | Credit Amount | ID # Call Handled | Vend Call Handled | Site Call Handled | Month Call Handled | Call Count Call Handled |
| July | Cambells | Clara | 1 | 20 | $329.00 | 1 | Cambells | Clara | June | 518 |
| August | Cambells | Clara | 1 | 18 | $245.06 | 1 | Cambells | Clara | August | 468 |
| June | Cambells | Clara | 1 | 16 | $260.96 | 1 | Cambells | Clara | July | 594 |
| July | Cambells | Clara | 1 | 20 | $329.00 | 1 | Cambells | Clara | August | 468 |
| August | Cambells | Clara | 1 | 18 | $245.06 | 1 | Cambells | Clara | June | 518 |
| June | Cambells | Clara | 1 | 16 | $260.96 | 1 | Cambells | Clara | August | 468 |
| July | Cambells | Clara | 1 | 20 | $329.00 | 1 | Cambells | Clara | June | 518 |
| August | Cambells | Clara | 1 | 18 | $245.06 | 1 | Cambells | Clara | July | 594 |
| June | Cambells | Clara | 1 | 16 | $260.96 | 1 | Cambells | Clara | July | 594 |
| May | Convergys | Clara | 4 | 8 | 31.51 | 4 | Cambells | Clara | May | 543 |
| July | Convergys | Clara | 4 | 17 | 107.45 | 4 | Cambells | Clara | May | 543 |
| June | Convergys | Clara | 4 | 22 | 164.01 | 4 | Cambells | Clara | June | 957 |
| September | Convergys | Clara | 4 | 19 | 198.34 | 4 | Cambells | Clara | July | 993 |
| November | Convergys | Clara | 4 | 9 | 54.5 | 4 | Cambells | Clara | September | 656 |
| December | Convergys | Clara | 4 | 7 | 85 | 4 | Cambells | Clara | September | 656 |
| August | Convergys | Clara | 4 | 17 | 128.3 | 4 | Cambells | Clara | October | 914 |
| October | Convergys | Clara | 4 | 21 | 190.48 | 4 | Cambells | Clara | May | 543 |
| November | Convergys | Clara | 4 | 9 | 54.5 | 4 | Cambells | Clara | May | 543 |
| December | Convergys | Clara | 4 | 7 | 85 | 4 | Cambells | Clara | August | 838 |
| August | Convergys | Clara | 4 | 17 | 128.3 | 4 | Cambells | Clara | September | 656 |
| October | Convergys | Clara | 4 | 21 | 190.48 | 4 | Cambells | Clara | October | 914 |
2 Replies
- Zubair_Muhammad
Community Champion
- dw700d
Post Patron
that did the trick thank you