Move data from row to column?

[ielamrani]

Hi, I don't know if this is possible but I appreciate any help in solving it.

I have table like this:

Name ID pcs Dim
Joe 123 obc Coc
Joe 123 obc Boc
Joe 123 bc Doc
Bob 323 bc Sat
Bob 323 bc Dork
Bob 323 bc Stark
Mike 423 ob ork
Mike 423 ob Dor


I am looking get a table with one line for each record and conbining the data from the last column. Something like this:

Name ID pcs Dim
Joe 123 obc Coc, Boc, Doc
Bob 323 obc Sat, Dork, Stark
Mike 423 ob ork, Dor

Thanks in advance

 

[pbaldy]

Typically done with functions like this:

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

 

[ielamrani]

Thanks fo the quick reply,

The query is returning this error message:

error 3464: Data type mismatch in cretiria expression.

Here is my query:

SELECT DISTINCT tbl.LastName, tbl.Indication, ConcatRelated("Indication","tbl","MRN = " & [MRN]) AS Expr1
FROM tbl

 

[pbaldy]

What is the data type of MRN?

 

[ielamrani]

Text

Thanks

 

[ielamrani]

I changed the MRN data type to number and it worked. Thank you so much.

 

[pbaldy]

No problem. You must have missed this from the link:

If the foreign key field is Text (not Number), include quote marks as delimiters, e.g.:
"[ForeignKeyFieldName] = """ & [PrimaryKeyFieldName] & """"

typically I would just do:

"[ForeignKeyFieldName] = '" & [PrimaryKeyFieldName] & "'"

 

[ielamrani]

Yes I did miss it.

Thank you so much for your help.