Distinct and Concatenate

[Gwen H]

I've been looking at similar questions posed on this discussion board, but I
can't quite figure how to do what I need. I have a Guarantors table with two
fields, Loan# and Guarantor. There are multiple entries for many loan numbers
in the table.

Loan# Guarantor
1111 John Smith
1111 Jane Doe
2222 Bill Johnson
2222 Joe's Bar and Grill
2222 Suzy Jones

I've set up a query that selects the distinct loan numbers from this table
and dumps it in another, TEMP. The next step is to concatenate every
occurrence of Guarantor from the original Guarantors table and update the
TEMP table with the new value. I need to join the Guarantors table and the
TEMP table on the Loan# field (which is included in both tables).

What's got me stuck is, one loan might have two guarantors, and therefore I
need to concatenate two guarantors together for that loan, then update this
new value in the TEMP table. Another loan might have five guarantors, so I
need to concatenate five guarantors together and update this new value in the
temp table.

This is the result I need in the TEMP table:

Note# Guarantor(s)
1111 John Smith, Jane Doe
2222 Bill Johnson, Joe's Bar & Grill, Suzy Jones

Can anyone help me get past this mental wall I've smashed into?

Thanks!
GwenH

 

[Michel Walsh]

Hi,


I assume you already got the TEMP table, with its second field an
alphanumerical field, but empty (Null value in it, for each and every
record), and the distinct Load number in the first field.


UPDATE temp INNER JOIN originalTable
ON temp.Loan = originalTable.Loan
SET temp.Guarantor = (temp.Guarantor + ", ") & originalTable.Guarantor



Hoping it may help,
Vanderghast, Access MVP

 

[MGFoster]

I've been looking at similar questions posed on this discussion board, but I
can't quite figure how to do what I need. I have a Guarantors table with two
fields, Loan# and Guarantor. There are multiple entries for many loan numbers
in the table.

Loan# Guarantor
1111 John Smith
1111 Jane Doe
2222 Bill Johnson
2222 Joe's Bar and Grill
2222 Suzy Jones

I've set up a query that selects the distinct loan numbers from this table
and dumps it in another, TEMP. The next step is to concatenate every
occurrence of Guarantor from the original Guarantors table and update the
TEMP table with the new value. I need to join the Guarantors table and the
TEMP table on the Loan# field (which is included in both tables).

What's got me stuck is, one loan might have two guarantors, and therefore I
need to concatenate two guarantors together for that loan, then update this
new value in the TEMP table. Another loan might have five guarantors, so I
need to concatenate five guarantors together and update this new value in the
temp table.

This is the result I need in the TEMP table:

Note# Guarantor(s)
1111 John Smith, Jane Doe
2222 Bill Johnson, Joe's Bar & Grill, Suzy Jones

Can anyone help me get past this mental wall I've smashed into?

Classic request. Here's the answer:

http://www.mvps.org/access/modules/mdl0004.htm

 

[Gwen H]

Oh wow! That did the trick. I was having a hard time following the logic of
the last bit of your update query SQL from the previous posts (plus I've been
working on this too long). The data I'm working with is pulled off Business
Objects, and the Guarantor field has spaces along with the actual text. When
I ran your update query, the spaces of course pushed the commas way out from
the text being concatenated. So, I did add one thing to the SQL - Trim().

UPDATE Guarantors_TEMP INNER JOIN guarantors ON [Guarantors_TEMP].[Loan#] =
[guarantors].[Loan#] SET Guarantors_TEMP.Guarantor =
(Guarantors_TEMP.Guarantor + ", ") & Trim(guarantors.Guarantor);

Thank you so very, very much.

GwenH