Was hoping someone could help me with the following:
I have 2 tables:
One called customer that contains the following fields:
| CompareID | Primary |
| 1 | 10 |
| 1 | 20 |
| 2 | 10 |
| 2 | 40 |
A second table called item
| Item | Primary | Status |
| 123456 | 10 | Y |
| 234567 | 20 | Y |
| 012345 | 40 | Y |
What I want to do is Join the 2 tables by the Primary Field for each CompareID and duplicate the records based on the ComapreID, Item, and Primary.
Results I'm trying to achieve: The First Line with CompareID = 1, Primary =10, Item = 123456, Status = Y comes in with the join. The second is what I need to duplicate. Since CompareID = 1 consists of primary 10 and 20 I need to create the record of CompareID =1, Primary =20, Item = 123456, Status = Y. I've tried the Append tool which gets me part of the way but I can not find a way to only duplicate the line for only the grouped CompareId. Thannks in advance for any help.
| Results | | | |
| CompareID | Primary | Item | Status |
| 1 | 10 | 123456 | Y |
| 1 | 20 | 123456 | Y |
| 1 | 20 | 234567 | Y |
| 1 | 10 | 234567 | Y |
| 2 | 10 | 123456 | Y |
| 2 | 10 | 012345 | Y |
| 2 | 40 | 012345 | Y |
| 2 | 40 | 123456 | Y |
