Concatenate Values in Microsoft Access

I have a table with two columns. The first has duplicates (column A), and the second (column B) does not.

I want to query the table in such a way that my result has unique values for A, and their corresponding values in B are all the values in B that were beside the duplicates in A.

To illustrate with a table,

A         B
food      spaghetti
food      cucumber
car       ford
car       honda
food      pickle

Would become:

A         B
food       spaghetti,cucumber,pickle
car        ford,honda

I'm working in Access (despite having little experience) because I have too many rows to work in Excel.

wizlog

Posted 2017-07-18T12:49:12.413

Reputation: 12 320

How are you querying the data? Are you running an Access query? Or using Excel to query the table? What have you tried so far? – CharlieRB – 2017-07-18T12:52:49.790

What I did in Excel was built a dictionary and when the key existed, I appended the new value to the existing one. From Access though, I've been trying to think how something similar would be done, but then I realized it isn't iterative. – wizlog – 2017-07-18T12:55:20.797

So you are trying to run an Access query then? – CharlieRB – 2017-07-18T12:56:49.697

Yes, I've moved my data to Access. I have a feeling such a simple query would only be a few lines, I'm just not sure where to start. – wizlog – 2017-07-18T12:57:31.243

Getting the unique values is quite simple by either using GROUP BY or DISTINCT. The first will likely be more useful. See also GROUP BY to combine/concat a column. The resulting query isn't going to be all that easy though.

– Seth – 2017-07-18T13:17:28.370

Great places to start! thank you @Seth and At McDonald's – wizlog – 2017-07-18T13:31:46.817

Concatenate Values in Microsoft Access

No answers