SQL codes: Combining two fields into one new field

Hello all,

I wanna ask about SQL codes on how to join 2 fields from 2 different tables in Access into 1 new field in new table proposedly. It is not that I want to combine those two fields into 1 new combined field like this. (many turned up giving this solution)

First Field (First Name) from Table A: John
Second Field (Last Name): Stuart
Third Field (First Name) & (Last Name): John Stuart

I have different case whereby I want

First Field (Product) from Table A: Book
Second Field (Product) from Table B: Pencil
Third Field (Product): Book

The two values would not be combined. In other way round, if the Product in Table B does not exist in Table A, it would be added to a new row in the new field (Third Field).

Hoping for feedbacks. TIA.

Post your answer or comment

comments powered by Disqus

I have done something like this before with SPPS and Excel.

What I wish to do is combine the 'TYPE' field with the 'USERID' field into a new field called 'CODE.' So, if someone's 'TYPE' was 01 and their 'USERID' was 0997, their 'CODE' would be 010997. Doing this would help me greatly with reports that I generate monthly.

Thanks in advance.


How do I combine two reports into one master report?

Thank you


I need to combine two number fields into a third field. The 'AutoNumber' field with the 'Class' field into a new field called 'Owner Number.' So, if someone's 'AutoNumber' was 1 and their 'Class' was 1, their 'OwnerNumber' would be 11. Thank you very much. I attached a Workbook.

I have two records:

field1 | field2 | field 3 | field4 |
A | 1.1 | 1 | |
A | 1.1 | | 2 |

Is there any way to combine to records into one like that

field1 | field2 | field 3 | field4 |
A | 1.1 | 1 | 2 |

Thanks in advance for any ideas

I have a table and would like to merge all the fields into one text field of the table after the insert.
I'm using adp (access/sql server).

When using standard sql the null values give a problem and i don't want to use the if clause as it will give too much coding.

fld 1 fld2 fld 3 mergefield
aaa bb aaabb
aa ee aaee

when using select fld1+fld2+fld3 the null values cause whole rows to disappear. Anybody knows an easy workaround?


I have a huge database (with 12000 lines) in which I would like to combine the values from 3 columns (fields) into a new column (field)



100 100


Should become:


Is there a way to do this via a maketable query ?

So I have a table containing 2 fields containing the first name and last name of a customer. I do need to add these names in the same table into a new field called Name. Can someone help me out?

How using SQL query in Acces to combine two fields into one.
For example:
Field3=Month & Date

This is my problem...I have a very simple query SELECT Tigers.[Error Code], Tigers.[Error Description] FROM Tigers; and it runs just fine however what I am trying to do is combine these two fields into one. All this information is in the same database. Thanking You in advance for your assistance in this matter.

I've been stuck on a problem for a few days, and despite furious Google searching, asking other DBAs, and blind attempts to force this to work, I can't seem to combine multiple queries into one.

When I attempt to do this with the wizard, I get the following error message: "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

I've also attempted to use SQL to create a union query, but I'm a little over my head when it comes to programming SQL. I'm not trained or certified to do so, and what I've picked up along the way has been through my past experience with Visual Basic and HTML.

My database contains logistics contracted rates. In particular, this section of it has rates for rail shipments. It contains origin, destination, stops from origin to destination (ranges from 0 stops - 2 stops), and volume of shipments for each route. I've attached the database with example data. It has been scrubbed to protect confidentiality. The rates are not real dollar amounts.

My ultimate goal is to create a report with the following: each route, with each stop along the way, with each route multiplied by volume to calculate total cost between each stop, and then total cost for each route.

I would like to create a query to generate this report. With a query, I can enter criteria to make it searchable from a form. For example, if I want to see rates between Mexico City and New York City, I would like to search those two cities and generate results.

There are also multiple contracts for each route, as the rates change on an annual basis.

Any help creating this query and/or report would be much appreciated!


First off, I thank you for reading. I'm in way over my head (always say yes to the boss right?) and think if I can get this one item I can at least pass.....

Building a DB in Access 2010, that has multiple tables that are all for data collection on employees. The primary key for the "main" table was impossible. Could not use last name or last,first as several family members usually work at one of our affiliates, and it is possible that all the data we will ever have on people are their names and some test scores.

So I came up with using a combination of Last Name, followed by basically an autonumber. I had 3000 name I had to import so this worked well for the import but now we will be using forms from here on out. I notice on the form, as soon as any entry is made, the autonumber field populates correctly with the next number. So the question is (keeping in mind this is my first shot at Access) does a possibility exist of the "EmpID" field auto populating while the form is still active or as it is saved? The first field to fill out on the form is Last name, and as soon as that is complete we have everything needed for the ID (Smith3011 for example) just could not find a solution searching through here.

To summarize: I would like to take two fields and combine into one

Last name:

And somehow on the form turn automatically into

EmpID:[LastName][IDNumber] without any spaces or punctuation.

Thank you VERY much for any help.!!

How to combine two tables in one table such that values are neither duplicated nor missed?

please, see the attached screen shot that show my question

i wanted to join Table5 and table6 such that the values of Field1 are all populated in the resulted table but neither duplicated nor overlapped!

How can i perform this kind of combination?

thank you in advance,


Jamal Attached Thumbnails   Attached Files M4.zip (66.0 KB, 0 views) Reply With Quote 10-28-2011, 06:39 AM #2 Robeen VIP Windows XP Access 2010 32bit Join Date Mar 2011 Location Tulsa, Oklahoma. Posts 1,500 There may be a simpler way, but try this:

Create a new query
1. Select Field1 from Table5.
2. Got to View -> SQL View.
You should see something like:
Code: Select Field1 FROM Table5; 3. Change the SQL in the same SQL pane so it looks like this:
Code: Select [Table5].[Field1] FROM Table5 UNION Select [Table6].[Field1] FROM Table6 ORDER BY [Table5].[Field1]; If you run this - you will get all the field1 values - no duplicates.
4. Save the Query and name it, for example, Query1.
5. Create a new Query.
6. Select - in this order: Query1, Table5 & Table6.
7. Join Field1 of Query1 to Field1 of Table5 & Field1 of Table6.
8. Right-click the Join lines and make the Join Properties of both option 2.
You should see an arrow pointing towards Table5 & Table6 on the join lines [in your new Query].
10. Select Field1 from Query1, Field2 from Table5 and Field3 from Table6.
11. Run the Query to verify that you get the results you need.
12. If you really need it to be a new table - convert Query1 into a MakeTable Query and run it to create your new table.

There may be a more sophisticated [a single query] way of creating the resultset [crosstab query, perhaps??] but I do not know off hand how to do that.

I hope this helps.

Hi all Im new to access. I have takin two excel files I have and imported them into access. One is called employees and the other one is employee list. employees table has info like address, Email, Date of birth, Hire Date ect. The Employee list has different info like Home phone, Cell Phone, Email and job title. I want to combine the two together and only have one without typing all the info from the smaller table to larger table. I want to join them by looking up the Email address and putting the cell phone num home phone and job title with the correct person.. Is this possible? Ive already created these new fields in the larger table i just need to get them moved from the small one now.. thanx for any help very new to access so be easy..

I have a table with many records that have all of the same values except for one field. I would like to combine these records into one with a multivalued field. Each of the similar records can be indentified by [call ID]

What I have:
Call ID | Type
1 4010
1 4250
2 4010
2 4032
3 1520
3 4010

What I would like:
Call ID | Type
1 4010, 4250
2 4010, 4032
3 1520, 4010

I added a multivalued field to the table and created another table as a lookup with all of the [type]s. I would like to use an append or make table query to accomplish this. I have zero knowledge of VBA and am comfortable with SQL.

Any help is appreciated!

First query = Sum Products:

	SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut)
AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;

Second query :

	SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc,
IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs,
[Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum
Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))


I am new to this forum and appreciate any help.

What I have is 5 text fields(used to hold phrases), all 40 characters long - they have an input mask set up so that there are 40 " " (spaces) always in the field, or any thing you type has spaces filling the remaing field to keep the total at 40 characters.

I need to combine these 5 text fields into one field with no spaces between fields, keeping it at 200 characters (5 x 40). This data can be displayed in either this table or another table.

any suggestions?

I have a field on my form that I need to combine my [city] [state] and [zip] fields into one field, BUT ALSO make that field editable. I already know how to simply combine those fields using =[city]&" "&[State]&", "&[Zip] in the control source, but although that displays everything in the correct format, I need to be able to edit it also.

So for example if the field were to display "New York NY, 10011" and I wanted to change it to "Manhattan NY, 10012" I could just type it into the field and it would update the apropriate fields.

Any suggestions???

Is it possible to join two or more text fields into one field? What I want, if possible, is to combine several fields of text such as "address", "street", "city", "state",...,' and have them combined as if they were one field such as "1 One Street City State". The combined field is a filing system I use. Right now the fields are in different places within the form so to save time, I would like to have them together.


Hi, I am trying to combine several records from ONE linked table into one record that have the same Order#.

My structure is as follows:
7788559 18 4 GW 4 X 10 AIR TIRE
806860 REF 3501050202020
806860 NO

I want it to look like this:
806860 REF 3501050202020 OPTIONS BEGIN NO

I am not that familiar with VBA, but I think it can be used to do this. I have searched, but I could not make any of the code I found work. Most of the code I found was based on two tables with a one to many relationship. My data is all in one table, so I kept getting errors.

Can someone please help?

Thank you!

My boss owns about 100 companies. Some of these companies have people to Contact while others don't. I have a table with the following fields:


Some companies don't have any contacts or info in these fields. I have created a query with expressions to combine the first and last names of each Contact:

Contact1: ([Contact1Last] & (", "+[Contact1First]))
Contact2: ([Contact2Last] & (", "+[Contact2First]))

Here's my problem: I want to be able to combine both of these expressions into ONE field or expression so that I can make a Report that sorts these names alphabetically. (Right now I can only sort "Contact1" alphabetically). I have tried using a Union Query that looks like this:

SELECT [Contact1] & [Contact2] FROM qryContacts;

The problem with this Union query is that it combines both Contact1 and Contact2 into a new expression within the same cell. Let's say for Company A Contact1 is "Smith, John" and Contact2 is "Williams, Ryan". The expression returns Smith, John Williams, Ryan in the same cell.

I want to basically combine the Contact 1 and Contact 2 Expressions so that I can make a report that looks like this:

Entity Name.......Contact........Phone........Cell....... ..Email

Right Now it looks like this:

Entity Name.......Contact1........C1Phone...........C1Cel l................C1Email
.......................Contact2........C2Phone.... .......C2Cell................C2Email

If anyone knows how to do this please let me know. Thanks a lot.


I found this code to copy all sheets ina spreadsheet into one
It works until a certain limit and then stops

I don't know much about vba excel so any help would be appreciate

I have 4 sheets and trying to combine them all into one sheet

Sub Combine()
Dim J As Integer

On Error Resume Next
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To (Sheets.Count + 1) ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2 )

End Sub

Hi all,

I have been given the task of cleaning up an old db. One thing I'd like to do is combine 3 text fields into 1 new field.
In the old db there was a form that included 3 memo text boxes, when the first one was full the user then started using the second and then the third. The relating Table fields are message, overflow message & overflow message extended.

I'd like to combine the fields into 1 and then design a new form to suit so that the memo text in the new db goes into 1 field only.

Invoiced Sales figs are listed in a Sales Table.
Journal adj figs for corrections are listed in a Jnls Table.
How do you combine two lists into one query column list so they can then be sorted, subtotalled, and displayed by date and rep etc?

Thanks for any help.



I have a table with employee numbers in four fields (Leader, Facilitator, ect...). How can I combine those numbers into one column trough a query?

Thanks in advance.


Not finding an answer? Try a Google search.