Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access - Loop and Concatenate

Posted on 2011-03-18
13
Medium Priority
?
416 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
  • 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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 1000 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 93

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 93

Expert Comment

by:Patrick Matthews
ID: 35210279
Gracias :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

927 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