Solved

MS Access - Loop and Concatenate

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

775 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