Concatenating Query 1

[eatwork]

Hi everyone,
I was wondering if there is anyway to concatenate a result set from a query into one field.
Eg. query returns:
quoteId includes:
1 chocolate
2 flour
3 salt

from that result set I would like to create a string:
"The quote includes chocolate, flour, salt"

Is this possible through only a query and without using methods/functions at all?
Thank you

 

[ByteMyzer]

No.

 

[lespaul]

you can check out FAQ701-4233 for a module that will return:

chocolate, flour, salt

you would then have to do some additional processing to add

"the quote includes"



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[eatwork]

Hello ByteMyzer and lespaul,
Thank you for your replies.

Lespaul,
I have a function that is similar to the one you provided in the forum. The reason I need the concatenation done within a single query is because I need to create a report via crystal reports and my database is access. Other MVP's have told me that it is best to have the database do all of the work in a query and I was attempting to accomplish this in a single query, as well as it being difficult to create functions to achieve this task in crystal reports. Would you have any other suggestions? thank you.

 

[lespaul]

So are you running crystal reports and calling an Access query or are you running Access trying to open a crystal report?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[eatwork]

Hi lespaul,
sorry for the confusion. I am creating a .net application that will utilize crystal reports. The data will be coming from an ms access db, query. I am running crystal reports and calling an access query. Thank you for the reply.

 

[dhookom]

I doubt my concatenate function will work outside of Access. There is a method that involves a "rollup" table and an update query.

Assuming we are using Northwind and want to create a comma separated list of product names for each category. Create a table
[tt][blue]
tblCategoryProducts
=====================
CategoryID Long Integer primary key
AllProducts Memo
[/blue][/tt]
Append all category IDs from the categories table resulting in 8 records in the new table.

Then create an update query with SQL of:

UPDATE Products INNER JOIN 
tblCategoryProducts ON Products.CategoryID = tblCategoryProducts.CategoryID 
SET tblCategoryProducts.AllProducts = [AllProducts] & [ProductName] & ", ";

Your resulting AllProducts field will contain the products with a comma between and after. You can run and update query to remove the trailing comma.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eatwork]

Hi Dhookom, thank you for your response,
Does that method basically involve a tempTable that continuously gets updated?

 

[dhookom]

I would set this up as a temporary table. Consider a process that:
[li]deletes all records from the tab[/li]
[li]appends the "group" primary key values[/li]
[li]runs an update that concatenates into a memo field[/li]
[li]runs an update that removes the trailing comma[/li]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]