Solved

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
17
26 Views
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.

ImageID333.jpg
ImageID334.jpg
ImageID335.jpg

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:
ImageID333.jpg|ImageID334.jpg|ImageID335.jpg


These need to be grouped by SkuID.

Table is ProductImages
Fields ProductImageFileNm and SkuID

Thanks!
0
Comment
Question by:Dustin Stanley
  • 8
  • 6
  • 3
17 Comments
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 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
'strive4peace
'loop through recordset and combine values to one string
   
   'NEEDS REFERENCE
   'a Microsoft DAO Library
   ' -- OR --
   ' Microsoft Office #.0 Access Database Engine Object Library
   
   'PARAMETERS
   '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
         .MoveNext
      Loop
   End With 'rs
      
   If Len(vAllValues) = 0 Then
      vAllValues = psNoValue
   End If
 
   
Proc_Exit:
   'close the recordset
   rs.Close
   'release the recordset variable
   Set rs = Nothing
    
   LoopAndCombine = Trim(Nz(vAllValues, ""))
   Exit Function
   
'if there is an error, the following code will execute
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
      & "   LoopAndCombine"
 
   Resume Proc_Exit
   Resume
End Function

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41903228
How would I use this in a query?
0
 
LVL 19
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

0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41903236
0
 

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
GROUP BY SkuID

Open in new window

0
 
LVL 57
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
GROUP BY SkuID

Jim.
0
 

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.
0
 

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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 57
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)

Jim.
0
 

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."
0
 
LVL 19
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" ...

WHERE
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.
0
 

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.
0
 

Author Closing Comment

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

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

WHERE
-99 is something you know you don't have
0
 

Author Comment

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

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

WHERE
-99 is something you know you don't have

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now