Join Tool To Add New Column

[giannademetroulakos]

I have 2 identical reports, one from June and one from July. I am trying to use the join tool to add the revenue column from the June file to the end of the July file. I want the output file to have a full list of all customers in one column (even if they were only a customer in one of the months and not both), another column with the revenue from July for each customer and a third column with the revenue from June for each customer. How should I configure the join tool to do this? Thank you

[cjaneczko]

One way is to add the join tool and join on the Customer Number. Bring in the the Revenue from both sheets. After that add a Union Tool and join in the Left, Right and Center of the join. This will bring every record in from both sheets whether there was a match or not. You should then have a column for Customer Number, June Rev and July Rev. 

[giannademetroulakos]

This is what I currently have set up, but the output has a Customer Name column and a Right Customer Name column. I am looking for one column that has all the customer names from both the right and left inputs

[cjaneczko]

Add a select tool after the Union and deselect the Right Customer.

[giannademetroulakos]

Would that get rid of any customers who were only in the right input file though?

[Rags1982]

Add the select AFTER the Right of the join but BEFORE the Union. Then Rename 'Right_Customer' to Customer. The Union will then work as intended. 

[jdminton]

The only one labeled as "Right_Customer" would be from the "J" output. The right and left will just be labeled "Customer". You can just deselect the right_customer in the join itself.

[giannademetroulakos]

I have realized a new problem. The customer name may be in the july file on 2 separate lines but each line has a different revenue value. I want those values to either be added together in the output and have one single line per customer or have the output have the 2 lines for the customer and keep the correct revenue values - right now my output file is giving me 2 lines for the customer but is picking one revenue value and using it for both lines

[jdminton]

You should be able to handle that by summarizing before the join.