Dustin Stanley
asked on
MS Access Query Concatenate Multiple Row Values Into A Single Row Or Field Used For Many Images For A Single Product
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|ImageI D335.jpg
These need to be grouped by SkuID.
Table is ProductImages
Fields ProductImageFileNm and SkuID
Thanks!
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.
These need to be grouped by SkuID.
Table is ProductImages
Fields ProductImageFileNm and SkuID
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Table name = ProductImages
ProductImageFileNm = Text Field
SkuID = Integer
Select SkuID, DConcat("ProductImageFileNm", "ProductImages", "[SkuID] = '" & SkuID & "'") AS ImageList
FROM ProductImages
GROUP BY SkuID
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("ProductImageFileN m", "ProductImages", "[SkuID] = " & [SkuID]) AS ImageList
FROM ProductImages
GROUP BY SkuID
Jim.
Does your app compile?
<<SkuID = Integer>>
Your treating it as text. It should be:
Select SkuID, DConcat("ProductImageFileN
FROM ProductImages
GROUP BY SkuID
Jim.
ASKER
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.
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.
ASKER
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.
<< 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("ProductImageFileN m", "ProductImages", "[SkuID] = '" & SkuID & "'")
so you were treating the value of SkuID as a string value.
Numeric's don't get them, so:
DConcat("ProductImageFileN m", "ProductImages", "[SkuID] = " & SkuID)
Jim.
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("ProductImageFileN
so you were treating the value of SkuID as a string value.
Numeric's don't get them, so:
DConcat("ProductImageFileN
Jim.
ASKER
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."
"[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."
>"[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("ProductImageFileN m" ...
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.
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("ProductImageFileN
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.
ASKER
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.
ASKER
Thank you very much!
you're welcome, Dustin ~ happy to help
my guess is that is how you pass the criteria:
"[SkuID] = " & nz(SkuID,-99)
WHERE
-99 is something you know you don't have
"[SkuID] = " & nz(SkuID,-99)
WHERE
-99 is something you know you don't have
ASKER
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!
you're welcome ~ happy to help
ASKER