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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA code to count rows 5
issue with the sender's name getting changed 1
Challenging Thinking 1
Combining multiple queries (on a single table), so the results appear side by side? 7
Can I run a Query or Pivot Table against this dataset? 2
Combine records to one field with commas 2
Query - Data Layout 5
outlook 2003 with multiple users 2

Back
Top