Access Help...

  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

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!
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Arcath
  • Graduate
  • Graduate
  • User avatar
  • Posts: 166
  • Loc: New England

Post 3+ Months Ago

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
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

AWESOME!!! Thanks so much arcath! That's perfect...I'll give that a shot!
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

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
  • Arcath
  • Graduate
  • Graduate
  • User avatar
  • Posts: 166
  • Loc: New England

Post 3+ Months Ago

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];
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

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?
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

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

They'll have to be permanate...unfortunately...
  • Arcath
  • Graduate
  • Graduate
  • User avatar
  • Posts: 166
  • Loc: New England

Post 3+ Months Ago

hmm, ok, I was trying to do it from memory. Let me create a sample DB and and get a working version for you.
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

Arcath thanks for the help!!!
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

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 :roll:

Code: [ Select ]
(SELECT [New_Test_Table]![Data_Description]&","&[New_Test_Table]![Detail_Description] AS [Combined_Description] FROM [New_Test_Table];)
  • Arcath
  • Graduate
  • Graduate
  • User avatar
  • Posts: 166
  • Loc: New England

Post 3+ Months Ago

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?
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

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!
  • Arcath
  • Graduate
  • Graduate
  • User avatar
  • Posts: 166
  • Loc: New England

Post 3+ Months Ago

I'm glad I was able to help :D

Post Information

  • Total Posts in this topic: 13 posts
  • Users browsing this forum: No registered users and 54 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.