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
Pencil

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.


Sponsored Links:



Hello,

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.

Tim
timbibo@mail.com




How do I combine two reports into one master report?

Thank you




Hello,

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.
Example

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

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




Hello,

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)

Example:

Group:
1234RP

Size:
100 100

Grade:
RPF

Should become:

ArticleCode
1234RP100100PRF

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:
Field1=Month
Field2=Date
===>
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!

Example.zip




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

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,

regards

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]

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


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


	Code:
	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))




Hello,

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.

TIA




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:
O5ORD ODTEXT
7788559 OPTIONS END
7788559 OPTIONS BEGIN
7788559 18 4 GW 4 X 10 AIR TIRE
806860 REF 3501050202020
806860 OPTIONS BEGIN
806860 NO

I want it to look like this:
O5ORD ODTEXT
7788559 OPTIONS END OPTIONS BEGIN 18 4 GW 4 X 10 AIR TIRE
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!