merging info together in one line

[balllian]

I have the following information in a table which is split into a number of rows. How will it be possible for this info to be be joined back together in one row.

Here is an example of the data i have in my table.

PRODUCTNO REMARKID REMARK
2954 11427 Fourteen bedroomed hotel situated by the old Penhelig harbour with views across
2954 11428 the estuary to the mountains beyond. Recommended by all leading hotels for quali
2954 11429 "ty food, wine and traditional ales.

 

[Remou]

There are a lot of posts on concatenation in these fora and more than one FAQ. Here is a link to start:
How to concatenate multiple child records into a single value
faq701-4233

 

[balllian]

thanks for this. the problem i am having is that the data i want to join together is all in one row. I know how to join info from two tables but im not too sure how to get this to work from one data row.

 

[Remou]

Did you have a chance to look at the link I posted? It refers to concatenating several rows into one.

 

[balllian]

i have tried the following.

SELECT productno,
Concatenate("SELECT remark FROM geninftext
WHERE productno =" & [remark]) as remark1

What do i need to change to this to get this to work?

 

[PHV]

Perhaps this ?
SELECT DISTINCT productno
, Concatenate("SELECT remark FROM geninftext WHERE productno=" & [productno] & " ORDER BY remarkid") AS remark1
FROM geninftext

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

thanks for this i'm now getting this error message

Undefined function 'Concatentate' in expression.

What does this mean?

 

[PHV]

Did you create the public function in a standard code module ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

No i was trying to write this in SQL. Can i not do this in SQL, does it have to be in a standard code module?

 

[lespaul]

Did you not read the FAQ? The very first line:
[tt]To use any function like this, open a new module. Copy the code from "Function Con..." to "End Function" into the new module. Select Debug|Compile to make sure there are no compile errors. Then save the module as "modConcatenate". You can then use the Concatenate() function as an expression in a query or control source or other places.[/tt]

So yes, you can do this in a query, you just have to copy and paste the code into a module as directed above!




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

 

[balllian]

i have tried and this doesn't work. i have tried this alongside this SQL

SELECT DISTINCT productno
, Concatenate("SELECT remark FROM geninftext WHERE productno=" & [productno] & " ORDER BY remarkid") AS remark1
FROM geninftext

and get the following error message.

Syntax error (missing operator) in query expression 'Product no Concatenate("Select remark From geninftext where productno='&[productno]&"Orderby remarkid")'

 

[PHV]

Your error message is irrelevant to the posted SQL ...

 

[balllian]

ok. so what else can i do to get the 'remark'to appear joined together????

 

[PHV]

Post the real SQL code and the real error message ...

 

[balllian]

SELECT DISTINCT productno
, Concatenate("SELECT remark FROM geninftext WHERE productno=" & [productno] & " ORDER BY remarkid") AS remark1
FROM geninftext

 

[dhookom]

Is productno text or numeric? If it is text, try:

Concatenate("SELECT remark FROM geninftext WHERE productno=""" & [productno] & """ ORDER BY remarkid") AS remark1

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]

 

[PHV]

And the real error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886