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
61 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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 …

839 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