Duplicate Records based on a grouping (Join)

[ABCDA]

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

[binuacs · Accepted solution]

@ABCDA One way of doing this with the help of batch macro

[ABCDA]

Thank you!