How do I combine a One to Many table into a Memo Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 07 Feb 2008 20:49 GMT

I'm trying to take a table that has two fields (Apart Unit Amenity +
Comments) and join them as a memo field. There may be several lines per
record so here is my question.

How can I change the data from:

Apt 1 - Pool - 2
Apt 1 - Spa - 2
Apt 1 - Garages
Apt 2 - Pool - 2
Apt 2 - Spa - 2
Apt 2 - Garages
etc

To read:
Apt1 - Pool 2, Spa 2, Garages
Apt2 - Pool 2, Spa 2, Garages
etc

Thanks.

Reply to this Message

John W. Vinson - 07 Feb 2008 22:07 GMT

>I'm trying to take a table that has two fields (Apart Unit Amenity +
>Comments) and join them as a memo field. There may be several lines per
[quoted text clipped - 16 lines]
>
>Thanks.

May I ask WHY you want to take a properly normalized table and jam all the
data together into a composite, harder to search, impossible to sort
composite?

If you just want to *display* the values as a comma separated string there is
no need to do so; just do it dynamically, using code like that found here:

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

If you really want to damage the logical structure of your database as you
describe, base an update query on this calculated query.

John W. Vinson [MVP]

Reply to this Message

Marshall Barton - 07 Feb 2008 22:14 GMT

>I'm trying to take a table that has two fields (Apart Unit Amenity +
>Comments) and join them as a memo field. There may be several lines per
[quoted text clipped - 14 lines]
>Apt2 - Pool 2, Spa 2, Garages
>etc

Use a Totals query with the Concatenate function (available
at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Funct
ion%20To%20Concatenate%20Child%20Records'

SELECT apt, Unit, Amenity,
                Concatenate("SELECT Comments FROM table
                        WHERE apt='" & apt & "' AND Unit='" & Unit
                                "' AND Amenity='" & Amenity & "' ")
FROM table
GROUP BY apt, Unit, Amenity

Signature

Marsh
MVP [MS Access]

Reply to this Message