Access 2007 Merging the content of a cell with duplicate id's 1

[shazellb]

Hello all,
I have a table with two fields in it. one of the fields named "Docid" has duplicate values in it and the other field named "Content" has unique content in it. I would like to merge the content in the field called "Content", whenever the docid's are duplicate, seperated by a ";" and only grab the first instance of the Docid.

For Example:

Docid | Content
0001 | Shaun
0001 | Shane
0002 | Mike
0003 | Robert

The query should return the following results.

Docid | Content
0001 | Shaun;Shane
0002 | Mike
0003 | Robert

I would like to recieve this in a query form, but if a sql statement is needed, then so be it.

Thank you in advance

 

[Golom]

Have a look at faq701-4233

 

[shazellb]

That seems helpful, but I would love to see it in query design form so that I could understand alot better. I hate to sound like a dummy but I am a noob and query design is the extet of my knowledge.

But thank you it is really appreciated.

 

[shazellb]

for example
can I just add the table to the window and writ something similar to this in the criteria area?
IIf("docid is the same", merge all results seperated by ";", "")

 

[Golom]

As I'm sure you noticed, that FAQ involves VB code so it would appear in a module and not in a query (either design or SQL view.)

You cannot (in Access at least) do this with SQL alone. You do need to use code.

 

[dhookom]

Your actual SQL might look like this assuming DocID is text

SELECT DocID, Concatenate("SELECT Content FROM tblWith2Fields clone WHERE Clone.DocID = '" & DocID & "'",";") As AllContent
FROM tblWith2Fields 
GROUP BY Concatenate("SELECT Content FROM tblWith2Fields clone WHERE Clone.DocID = '" & DocID & "'",";");

Duane
Hook'D on Access
MS Access MVP

 

[shazellb]

you are the best Dhookom. it works perfectly and I can understand what it is doing.

 

[PHV]

A simpler way:

SELECT DISTINCT DocID, Concatenate("SELECT Content FROM tblWith2Fields WHERE DocID='" & DocID & "'",";") As AllContent
FROM tblWith2Fields

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shazellb]

Thanks PHV, I will try as soon as I get to my computer.

 

[shazellb]

Thank you, but I am habving some problems running it. Please advise.

 

[dhookom]

What problems? Are you suggesting that my earlier SQL "works perfectly" while PH's doesn't? They should both work. If not, can you provide your actual SQL view and what makes you think it didn't work?

Duane
Hook'D on Access
MS Access MVP