Solved

MS Access - Loop and Concatenate

Posted on 2011-03-18
13
407 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 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