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
54 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

831 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