Access Help...

All:
I'm an old faithful of the web development portion of this forum. But, my question now pertains to Access...I know that doesn't fall sqare under windows but I wasn't sure where to put it, or even if I could find help here.

Current issue: I need to combine two columns in a query into a new column. Can this be done? How?

If anyone knows the answer I'm all ears. Else, if anyone knows a good Access forum or place to get help, I'd be greatful for you input.

Thanks in advance!

SELECT [Table1]![LName] & ", " & [Table1]![FName] AS FullName FROM [Table1];

Would end up like: Jones, James


SELECT [Table1]![FName] & " " & [Table1]![LName] AS FullNameFROM [Table1];

Would end up like: James Jones

AWESOME!!! Thanks so much arcath! That's perfect...I'll give that a shot!

I put those two selects in the criteria of a NEW field?

Every time I try to make a new field it tells me I have to choose a field from the table I'm doing this query to...I know there's a way to add a new field I'm just missing it

There are different ways to create the query, you are in the field chooser view, The text I posted was a sample SQL statement.

Click on View and then SQL View. Also the SELECT and FROM statements should be on seperate lines, they didn't come out right in my previous post.

This is just a sample and will have to be changed to match the field names you are using:

SELECT [Table1]![LName] & ", " & [Table1]![FName] AS FullName
FROM [Table1];

The query above doesn't actually create or update a new field. It creates a "virtual column" by combining the existing data from the two table fields. Once the query is closed that information goes bye-bye. Each time you run the query it is recreated with current data.

Did you need it to be permanent or will that suffice?

I got an error back...said "at most one record could be returned by this query"

They'll have to be permanate...unfortunately...

hmm, ok, I was trying to do it from memory. Let me create a sample DB and and get a working version for you.

Arcath thanks for the help!!!

I gave this a shot...[Combined_Description] is the field I created where I want the merged data to populate...again I got back at most it can only return one record though

Ok, this is what I got.

TEST DATA: Table name:TEST

Field1 Field2 Field3
------- -------- --------
Joe Smith
Dan Jones
Kathy Green


=======================

SQL statment:
SELECT [TEST]![Field1] & ", " & [TEST]![Field2] AS Field3
FROM TEST;

Copy from design view:
Field3: [TEST]![Field1] & ", " & [TEST]![Field2]

=====================
Output:

Field3
--------
FNAME, LNAME
Joe, Smith
Dan, Jones
Kathy, Green


Most of the data manipulation I have in Access is done in VBA and usually only effect one cell (removing dashes from SSN) so I'm probably not the person to ask for a definative answer. Anyone else on Ozzu got an easy way to make this work?

I got it man! Thanks!! I was adding your code into the criteria field...that's what was messing me up. I didn't input it into the SQL...DUH!!

It worked perfectly! Thanks again for your help!

I'm glad I was able to help