Append contents of a field with contents of another field

[kdburger]

This should be easy for all you SQL gurus. I'm a novice, and I have a SQL database with a table that has a field matter, with contents :MATTER. (Yes, colon, then variable MATTER. For instance, ":Burger Corporate Formation"). I need to append the beginning of that field with the contents of another field, CLIENT, (for instance, "Burger, Kathleen") in the very same table. My result will be CLIENT:MATTER in one field. There will be no null contents in these fields in any of the rows. I need to do this to link the database with QuickBooks. I can't determine what the query should be, and would appreciate any help. Thanks. ~ Kathy

 

[PWise]

Try

Update tablename Set matter = nz(client,"") & matter

 

[KirbyWallace]

Not sure exactly hat you are trying to do, but maybe this?

SELECT [client] & ":" & [matter] AS CLIENT_MATTER

IF you need to be concerned with "appending the BEGINING of that field", then, perhaps change "[client]" to

SELECT left(instr([client]," ")-1)& ":" & [matter]
AS CLIENT_MATTER


It's kind of hard to tell what you are wanting to know. But hope that helps.

 

[kdburger]

Thanks for the quick response. This is so over my head, I'm sorry I didn't explain very well what I need.
My database name is dbuser. My table name is dbuser.matter. In the table there are four fields; sysID, client, matter_ref, and custom7. What I need is for custom7 to have the contents of client, a colon, and the contents of matter_ref for each row in the table.

 

[missinglinq]

You do realize that this is an MS Access database forum? Is Access being used here as the front end? Or have you wandered into the wrong place?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[kdburger]

Well, I guess I AM in the wrong place. I'm looking for SQL advice.

 

[PWise]

if you are useing access as a front end try

Update tablename Set custom7= nz(client,"") & ":" & matter

 

[randy700]

You are duplicating data by placing the same information in a separate field. Try using a query instead. Something like:

SELECT client & ":" & matter_ref As Custom7 FROM dbuser

Randy

 

[kdburger]

Thank you all for your suggestions. Once the technician allows me to install SQL Studio Express or Enterprise Manager, I'll be able to test them and see what works best.