[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


MS Access Query Concatenate Multiple Row Values Into A Single Row Or Field Used For Many Images For A Single Product

Posted on 2016-11-27
Medium Priority
Last Modified: 2016-11-28
I am lost on this one. I am working on queries for an export file. I have Products and each Product can have several photos maybe even 12 or so. Each Image for a single product has a file name. Example:

SkuID 777 has 3 photos.


How can I create a query with a concatenated field with the File Names into a single row. Also with a separator such as (, or |).

SKU Images:

These need to be grouped by SkuID.

Table is ProductImages
Fields ProductImageFileNm and SkuID

Question by:Dustin Stanley
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
LVL 22

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 1000 total points
ID: 41903227
Hi Dustin,
here is a procedure to test:
'~~~~~~~~~~~~~~~~ testLoopAndCombine
Sub testLoopAndCombine()
'test LoopAndCombine
   Dim sTablename As String _
      , sIDFieldname As String _
      , sTextFieldname As String _
      , nValueID As Long _
      , sFieldSortBy As String
   sTablename = "MyTablename"
   sIDFieldname = "MyNumericForeignKeyFieldname" 'if FK is not a number, you will need to add delimiters to LoopAndCombine where it is referenced
   sTextFieldname = "Description of Fieldname"
   nValueID = 138 'some number you know is in the table
   sFieldSortBy = "FieldnameToSortBy"
   MsgBox LoopAndCombine(sTablename, sIDFieldname, sTextFieldname, nValueID, , , , sFieldSortBy)

End Sub

Open in new window

and here is the code:
'~~~~~~~~~~~~~~~~ LoopAndCombine
Function LoopAndCombine( _
   psTablename As String _
   , psIDFieldname As String _
   , psTextFieldname As String _
   , pnValueID As Long _
   , Optional psWhere As String = "" _
   , Optional psDeli As String = ", " _
   , Optional psNoValue As String = "" _
   , Optional psOrderBy As String = "" _
   ) As String
'loop through recordset and combine values to one string
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
   'psTablename --> table name (or query name) to get list from
   'psIDFieldname --> fieldname to link on (ie: "BookID")
   'psTextFieldname --> fieldname to combine (ie: "PageNumber")
   'pnValueID --> actual value of ID for this iteration ( ie: [BookID])
   'psWhere, Optional  --> more criteria (ie: "Year(PubDate) = 2006")
   'psDeli, Optional  --> delimiter other than comma (ie: ";", Chr(13) & Chr(10))
   'psNoValue, Optional  --> value to use if no data (ie: "No Pages")
   'psOrderBy, Optional  --> fieldlist to Order By
   'Set up error handler
   On Error GoTo Proc_Err
   'dimension variables
   Dim rs As DAO.Recordset _
      , vAllValues As Variant _
      , sSQL As String
   vAllValues = Null
   sSQL = "SELECT [" & psTextFieldname & "] " _
       & " FROM [" & psTablename & "]" _
       & " WHERE [" & psIDFieldname _
       & "] = " & pnValueID _
       & IIf(Len(psWhere) > 0, " AND " & psWhere, "") _
       & IIf(Len(psOrderBy) > 0, " ORDER BY " & psOrderBy, "") _
       & ";"
   'open the recordset
   Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
   'loop through the recordset until the end
   With rs
      Do While Not rs.EOF
         If Not IsNull(.Fields(psTextFieldname)) Then
            '~~~~~~~~~~~~~~~~~~~~~~~~~ CHOOSE ONE
            '---- if field value is numeric
            vAllValues = (vAllValues + psDeli) _
             & Trim(.Fields(psTextFieldname))
            '---- uncomment if you want quotes around data
            'vAllValues = (vAllValues + psDeli) _
             & " '" & Trim(.Fields(psTextFieldname)) & "'"
          End If
   End With 'rs
   If Len(vAllValues) = 0 Then
      vAllValues = psNoValue
   End If
   'close the recordset
   'release the recordset variable
   Set rs = Nothing
   LoopAndCombine = Trim(Nz(vAllValues, ""))
   Exit Function
'if there is an error, the following code will execute
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
      & "   LoopAndCombine"
   Resume Proc_Exit
End Function

Open in new window


Author Comment

by:Dustin Stanley
ID: 41903228
How would I use this in a query?
LVL 22
ID: 41903235
substitute values into the "test" code till you get what you want. A query can't skip parameters so you would do something like this:
MyCalculatedFieldname: LoopAndCombine("ProductImages", "SkuID", "ProductImageFileNm", Products.skuID, "" , ", " , "No image" , "ProductImageFileNm") 

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 41903236

Author Comment

by:Dustin Stanley
ID: 41903346
Ok I have been trying the module Jim provided and I am having a little trouble. It looks to be the best option for what I am trying to do. But Some Reason it keeps just saying error. Its grouping but in the New field I called ImageList it just says error. This is a single table query

Table name = ProductImages
ProductImageFileNm = Text Field
SkuID = Integer

Select SkuID, DConcat("ProductImageFileNm", "ProductImages", "[SkuID] = '" & SkuID & "'") AS ImageList
FROM ProductImages

Open in new window

LVL 58
ID: 41903366
Did you put it in a module by itself and name the module something other than DConcat?

Does your app compile?

<<SkuID = Integer>>

   Your treating it as text.  It should be:

Select SkuID, DConcat("ProductImageFileNm", "ProductImages", "[SkuID] = " & [SkuID]) AS ImageList
FROM ProductImages


Author Comment

by:Dustin Stanley
ID: 41903374
Yes that fixed it. Thanks Jim!

OK.... I absolutely knew it was a text integer error and you stated that. But can you please explain exactly what the issue was. I have tried to figure it out and couldn't. Thanks so much.

Author Comment

by:Dustin Stanley
ID: 41903376
Oh yeah and by the way I store just the image file name. So how can I concatenate a url on the front of  ProductImageFileNm while using this code? Thanks.
LVL 58
ID: 41903379
<< But can you please explain exactly what the issue was.>>

When you have a text (string) value, it must be enclosed with ' (apostrophe or what some call a "single quote"), or with " (Quote).

Your original statement had this:

DConcat("ProductImageFileNm", "ProductImages", "[SkuID] = '" & SkuID & "'")

 so you were treating the value of SkuID as a string value.  

Numeric's don't get them, so:

DConcat("ProductImageFileNm", "ProductImages", "[SkuID] = " & SkuID)


Author Comment

by:Dustin Stanley
ID: 41903383
Ok Thanks but what is a little confusing there is...

"[SkuID] = " has Quotes

I thought quotes treats it like a string?

If so why is it a string but not the other?

Also I don't know if you seen my last comment or not but

"I store just the image file name. So how can I concatenate a url on the front of  ProductImageFileNm while using this code? Thanks."
LVL 22
ID: 41903388
>"[SkuID] = " has Quotes
I thought quotes treats it like a string?
If so why is it a string but not the other?

quotes make it a LITERAL value -- meaning you need it to say exactly what is in the quoted string

> "I store just the image file name. So how can I concatenate a url on the front of  ProductImageFileNm while using this code? Thanks."

perhaps change DConcat("ProductImageFileNm" ...
to DConcat("ImageURL" & "ProductImageFileNm" ...

ImageURL is your fieldname -- change if it is different. Just guessing since his code is a it different than mine.
You may also need to concatenate a slash (delimited with quotes since you need it to say exactly that)  between the URL and filename if the URL if a folder and does not have one on the end.

Author Comment

by:Dustin Stanley
ID: 41903393
Oh I see thanks Crystal. I will try that DConcat("ImageURL" & "ProductImageFileNm" later tonight. What I just did was made a query and Concatenated it in that query with a field name Image URL then changed ProductImageFileNm with ImageURL from the new query.

Author Closing Comment

by:Dustin Stanley
ID: 41903394
Thank you very much!
LVL 22
ID: 41903395
you're welcome, Dustin ~ happy to help
LVL 22
ID: 41903430
my guess is that is how you pass the criteria:

"[SkuID] = " & nz(SkuID,-99)

-99 is something you know you don't have

Author Comment

by:Dustin Stanley
ID: 41904511
my guess is that is how you pass the criteria:

"[SkuID] = " & nz(SkuID,-99)

-99 is something you know you don't have

It works Thanks!
LVL 22
ID: 41904512
you're welcome ~ happy to help

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question