Concatenate strings from different records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan
 
jonnymarketingguy said:
I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan
Click to expand...
 
Since your PIN field is text, you must delimit it with quotes:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=""" & [PIN]
& """")

--
Duane Hookom
MS Access MVP


jonnymarketingguy said:
I found Duane Hookum's link
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane and got
it
going. But I am getting an error message: Syntax error in query expression
'PIN=$TMPTst1'

The query expression i am using is:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=" &
[PIN])

Is there a problem with the dollar sign? If so how do I get around it?

Thanks,
Jonathan


jonnymarketingguy said:
I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the
user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan
Click to expand...
Click to expand...
 
Thanks everyone got it working.

Duane Hookom said:
Since your PIN field is text, you must delimit it with quotes:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=""" & [PIN]
& """")

--
Duane Hookom
MS Access MVP


jonnymarketingguy said:
I found Duane Hookum's link
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane and got
it
going. But I am getting an error message: Syntax error in query expression
'PIN=$TMPTst1'

The query expression i am using is:
Filters: Concatenate("SELECT filter from tblCategories WHERE PIN=" &
[PIN])

Is there a problem with the dollar sign? If so how do I get around it?

Thanks,
Jonathan


jonnymarketingguy said:
I have a query that returns categories for users. A user can belong to
different categories. I want to concatenate all the categories that the
user
belongs to.

For example the query returns

User Category
User1 5445
User1 5500
User2 5448
User3 5500

I would like to concatenate those to be
User Category
User1 5445, 5500
User2 5448
User3 5500

Is this possible?

Thanks,
Jonathan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top