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
46 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 VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

919 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

20 Experts available now in Live!

Get 1:1 Help Now