Solved

MS Access - Loop and Concatenate

Posted on 2011-03-18
13
410 Views
Last Modified: 2012-05-11
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
Comment
Question by:johnnyloff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 35169806
What's a 'Product'?
0
 
LVL 21
ID: 35169843
0
 

Author Comment

by:johnnyloff
ID: 35169848
Sorry.. the Product is the Variety.variety_name

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

 

Author Comment

by:johnnyloff
ID: 35169871
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
 
LVL 44

Accepted Solution

by:
GRayL earned 250 total points
ID: 35169916
0
 

Author Comment

by:johnnyloff
ID: 35202623
...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
 
LVL 44

Expert Comment

by:GRayL
ID: 35203665
It was written by a 'real' Expert, so if you are having problems, I suggest it is how ;you are implementing it.
0
 

Author Comment

by:johnnyloff
ID: 35208761
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
 

Author Comment

by:johnnyloff
ID: 35209095
GrayL - Simple oversight as I thought and now I got it to work.  Thank you
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 35209652
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
 
LVL 44

Expert Comment

by:GRayL
ID: 35209921
Thanks, glad to help.  But the real kudos belong to Patrick, the author.
0
 

Author Comment

by:johnnyloff
ID: 35210215
Patrick - No problem.  I never bothered to click on those before but I gave you a Yes.  Thank you!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 35210279
Gracias :)
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Omit After Update event 5 46
Access 2010 Form - Turn Updating On - Off 31 98
Microsoft Access 32-bit or 64-bit? 11 59
Combo box with two columns question 7 40
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

734 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