Solved

MS Access - Loop and Concatenate

Posted on 2011-03-18
13
405 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

24 Experts available now in Live!

Get 1:1 Help Now