How to join data and have multiple results?

[Inactive User]

I have a list of journal entries on one side and I have a list accounts associated with each entry on the other. Is there a way to use the join tool to combine the accounts of each entry?

Ex:

List 1

JE0001	1100: Revenue
JE0001	1200: Cash
JE0001	4000: Accounts Receivable
JE0005	XX
JE0005	XX

List 2

JE0001

JE0002

JE0003

Desired End Result

JE0001

1100: Revenue, 1200: Cash, 4000: Accounts Receivable

[JohnJPS]

Hi @Inactive User,

Do the join as usual, on the first column.  The middle output will have three rows, all for JE001. Then do a Cross Tab to concatenate the multiple rows into a single row. (See attachment for this done on your sample data).

Hope that helps!

John

[Inactive User]

Thanks for the reply!

I used your advice but the crosstab output something along the lines of the following...

JE Number	JE002	JE003	JE004	JE005
JE002	Revenue
JE003		Revenue
JE004			Revenue, Accounts Receivable
JE005				Accounts Receivable

Is there a way to only have one column looking something like...

JE Number	Accounts
JE001	Revenue
JE002	Revenue
JE003	Revenue, Accounts Receivable
JE004	Accounts Receivable

[JohnJPS · Accepted solution]

Understood, this can be done by adding a generic field label and using that as the Field Header in the Cross-Tab.  Please see the attached for the actual workflow.

[echuong]

You can also achieve this by just using the summarize tool to streamline things. Just do a group by for the JE number and a concatenate for the description. You can change the default seperator to a comma with space (from a comma) for visual clarity.

[JohnJPS]

@echuong is absolutely spot on - recommend adding his solution to "Accepted."

 - John