How to concatenate multiple records from one table and display on a form

[MilaK]

Hello,

I would like to concatenate multiple related records from one table and display them on a form (please see the attached example).

Is there a way to do this without creating another table? If so, how?

Thanks, Mila

 

[pbaldy]

You can base the form on a query that uses this function:

http://allenbrowne.com/func-concat.html

 

[MilaK]

The described function only takes in one field as the parameter. I would like to concatenate three fields for related records. Can this be done in Access? Thanks

 

[pbaldy]

Not so, as noted in the link:

Any valid WHERE clause is permitted

so you simply need to provide that. In your case, two fields separated by And.

 

[pbaldy]

Oh, and it should be easy to modify it to return more than one field.

 

[MilaK]

I've found a post that uses ADODB.Connection and not DAO.Recordset for this, which is better?

Thanks, Mila

http://stackoverflow.com/questions/5174362/microsoft-access-condense-multiple-lines-in-a-table/5174843#5174843

 

[pbaldy]

I'd say to use whichever you're more comfortable with.

 

[MilaK]

I’ve tried the other function that uses ADOBD connection but got an error message on ADODB.Connection that it couldn’t find the connection.

Allen Browne’s function produces an output, however, I’m not sure how to modify the function to get the desired output.

Here is the query that executed:

SELECT tbl_failed_amplicons.run_name, tbl_failed_amplicons.sample_name, ConcatRelated("gene","tbl_failed_amplicons","[sample_id] = """ & [sample_id] & """") AS Expr2, [gene] & "  " & [exon] & "  " & [depth] AS Exp3 FROM tbl_failed_amplicons;

(please see screenshot of the output)

It returns only genes string together and I would like to string [gene] & [exon] & [depth].

I’ve tried to add additional parameters to “where” clause "[sample_id] = """ & [sample_id] And "[gene] = """ & [gene] but it didn’t work. I’ve crashed Access several times and had to terminate the process.

Could you please suggest how to modify the where clause to get the desired output? Thanks, Mila

 

[pbaldy]

I'm not on a computer right now, but it would look like the criteria portion here:

http://access.mvps.org/access/general/gen0018.htm

Note the multiple criteria example at the bottom.

 

[MilaK]

Ok, I solved it. I used two queries:

First:

SELECT tbl_failed_amplicons.sample_name, tbl_failed_amplicons.run_name, tbl_failed_amplicons.sample_id, [gene] & "  " & [exon] & "  " & [depth] AS Exp
FROM tbl_failed_amplicons;

Second:

SELECT Distinct ConcatRelated("Exp","qry_first_failed_amps","[sample_id] = """ & [sample_id] & """") AS Expr2
FROM qry_first_failed_amps;

Thanks

 

[pbaldy]

Glad you got it working.