Combining text fields

I am sort of a newbie to SQL, so hopefully someone can help me out.

All I wanted is to combine two or more text fields together, provided they have the same ID.

For example, if I can generate a table like this through a query:

Book ID Description
------- --------------
6566 New
6566 Brown cover
6444 Missing

What I wanted is to be able to merge the “Description” together into something like this:

Book ID Description
------- --------------
6566 New + Brown cover
6444 Missing

Can anyone please tell me how I can do this in SQL?

Note: My develop environment is MS Access 97.

you can't do this with sql, you need application code

Thanks for the reply, much appreciated.

Still bummer

Then can you please give me any hints on I can possibly do this in code?

Since this is done in Access, I know I can program a module in Visual Basic.

I know a bit about Visual Basic. The "&" operator can append two or more strings together. I guess I can assign two variables; one for the current ID and the other is the next ID (ie. read ahead), if they are the same then combine the strings.

However, how can I retrieve a record and particular fields in code? Also, how to move to next record then move back? Any good reference on the web?


One other question, the actual sources of my database are from either Oracle or DB2. I use the SQL "INTO" to extract a subset of the tables to my PC through Access. Any ways I can perform similar coding in these two environments? Bearing in mind I only have read access to the source databases.

Thanks again ...

Hadwin.