concatenating records in one field

DBM

New member
Local time
Today, 08:02
Joined
Nov 4, 2012
Messages
5
I am trying to concatenate records in a field with commas to separate. the field sometimes has repeating records that I do not want to concatenate into the new field, I only want to concatenate one occurrence of each different value into the new field an example would be
Landform1 (this is the field name) of the Landscape Table: the records in the field are:
hills
hills
hills
mountains
drainages
mountains

and I want: hills, mountains, drainages.as a string in the new field "Landform2" of the Landscape Table... Can anyone help?
 
I think i can retain integrity of the values if I concatenate in a different table than the one i was originally trying. More info: I have three tables A, B, C. A has a one to many relationship with B, and B has a one to many relationship with C I would like to concatenate records that occur in the column of table C and place them in a new field in the table A. By doing so, I shouldn't be breaking the normalization criteria through the first two at least (refer to relational database doc in your original reply). There is a Primary Key in Table B that links to the Foreign Key in Table C (this has the multiple records that I am interested in) Table A has a key field that is one to one with the same field in Table B (that is how i am relating the tables).
 
??? Not sure I understand. How about posting a jpg of your tables and relationships?

To help readers you may wish to talk about your tables, what they contain, what your applications is about etc.

A,B and C don't convey much.
 
Thank You. Were you able to view the example table? I did not have internet connectivity over the weekend and only spotty while trying to communicate with you.
I think that I can use the Select Distinct after I get the concatenation to look like the example. I tried to use the Concatrelated function. It has been uploaded to my database, but I think I am not applying it correctly, probably due to my inexperience with access and the terminology used in the function. for the example table in the zip file (attached previously) I was applying the function in a new query as below:

Expr1: [concatenated1] «Expr» ConcatRelated ([attribute],[Example],[attribute]=?,[symbol1], ", ")

When I do this I get a parameter box for Example and ? then the query runs and I get error for the concatenated1 column values... if you view the table example you will see that I am entering [attribute] for field, [Example] for the table, [attribute]=? for the Where clause (I used ? to get all values in [attribute] field), and [symbol1] for the sort by and ", " for the separator (how I want to separate the concatenated values)...
I know I am missing something very important here, but don't have enough experience to know what it is... Can you help?
 

Users who are viewing this thread

Back
Top Bottom