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

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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.
 
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.
Click to expand...

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]
 
Eric said:
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
Click to expand...


Use a Totals query with the Concatenate function (available
at
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

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
 
Back
Top