Join Rows together in Access

[virgovibe]

Hello,

I have a table which I'd like to join text fields together based on One "Opportunity #" - I'm unfamiliar with SQL so I use a bunch of Functions in Access. How can I join the Max Service Line together to show Technology, Media?



Table Name: Max Service Line

Opp Number	Max Service Line
5555	Technology
5555	Loyalty

[pbaldy]

How about:

http://allenbrowne.com/func-concat.html

[virgovibe]

How about:

http://allenbrowne.com/func-concat.html

I've taken a look at that but I was unable to get it to work. I'm not very experienced in SQL code so I kept having errors in my code re-write.

[pbaldy]

Hard to fix errors without knowing what they are, or the specific way you incorporated this function. It would be called from a query that returned a single record for each opportunity number.

[virgovibe]

Would you be able to provide an example of the code using the details I provided? I’m sure the the attempt I made was incorrect since I didn’t understand the SQL code so it wouldn’t help to show it.

Table name: max service line
Unique field (its listed once): opportunity number
Field which can have multiple results which I’d like to “join”: max service line

[June7]

Never hurts to show attempt for analysis.

The ConcatRelated function can be called in query or textbox.

In query like:

SELECT [opportunity number], First(ConcatRelated("[max service line]", "[MAX SERVICE LINE]", "[opportunity number]='" & [opportunity number] & "'")) AS Services FROM [MAX SERVICE LINE] GROUP BY [opportunity number];

[virgovibe]

Never hurts to show attempt for analysis.

The ConcatRelated function can be called in query or textbox.

In query like:

SELECT [opportunity number], First(ConcatRelated("[max service line]", "[MAX SERVICE LINE]", "[opportunity number]='" & [opportunity number] & "'")) AS Services FROM [MAX SERVICE LINE] GROUP BY [opportunity number];

I get an error message: Undefined function "concatrelated" in expression

[pbaldy]

Did you follow the "how to use this function" instructions?

[virgovibe]

Not sure whats going wrong here..

[pbaldy]

What june7 gave you was a query:

In query like: