• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

MS Access - Loop and Concatenate

I have a query that gives me a list of products by country.  I would like to concatenate the products separated by a comma onto one line for each country.  How do I create a loop so that I can use this in a report?

Here's the query

SELECT DISTINCT COUNTRY.COUNTRY_DESC, VARIETY.VARIETY_NAME, VARIETY.VARIETY_ORG_ID
FROM (RPT_LINE_ITEM INNER JOIN COUNTRY ON RPT_LINE_ITEM.RPT_LINE_ITEM_COUNTRY_ID = COUNTRY.COUNTRY_ID) LEFT JOIN VARIETY ON RPT_LINE_ITEM.RPT_LINE_ITEM_VARIETY_ID = VARIETY.VARIETY_ID
WHERE (((VARIETY.VARIETY_ORG_ID)=968))
ORDER BY COUNTRY.COUNTRY_DESC;
0
johnnyloff
Asked:
johnnyloff
  • 6
  • 4
  • 2
  • +1
1 Solution
 
GRayLCommented:
What's a 'Product'?
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
0
 
johnnyloffAuthor Commented:
Sorry.. the Product is the Variety.variety_name

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
johnnyloffAuthor Commented:
I'm surprised there's not an easier way to do it but that one may come in handy.  I'll try it out after the weekend when I get back on this project.
0
 
johnnyloffAuthor Commented:
...super busy week...

GRayL - I'm having trouble with error messages when I attempt that.  I'm still looking through the related posts to see if I can figure it out.  It looks fairly straightforward.

Thehitechcoach - I haven't tried that option yet but will soon
0
 
GRayLCommented:
It was written by a 'real' Expert, so if you are having problems, I suggest it is how ;you are implementing it.
0
 
johnnyloffAuthor Commented:
GRayL - I have no doubt my implementation is the problem and I alluded to as much in my post.  I have never claimed to be more than a beginner who has taken on too much.  Maybe you woke up on the wrong side of the bed?

In any case, I'll figure it out and be specific with any issues I may have.  I simply responded to keep the question from being closed prematurely.
0
 
johnnyloffAuthor Commented:
GrayL - Simple oversight as I thought and now I got it to work.  Thank you
0
 
Patrick MatthewsCommented:
johnnyloff,

Glad you found that function useful!  If you haven't done it already, I'd really appreciate it if you could go back to the article and click Yes on 'Was this helpful'.

GRayL,

Thanks for thinking highly enough of my article to recommend it to others!  That is surely the best compliment I could hope to receive.

Cheers,

Patrick
0
 
GRayLCommented:
Thanks, glad to help.  But the real kudos belong to Patrick, the author.
0
 
johnnyloffAuthor Commented:
Patrick - No problem.  I never bothered to click on those before but I gave you a Yes.  Thank you!
0
 
Patrick MatthewsCommented:
Gracias :)
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now