SQL codes: Combining two fields into one new field

[SyafiqIzzat]

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.

 

[Guus2005]

Your question is not very clear.

If TableA has 2 fields: FirstName and LastName and you want to combine these then you will need a query like this:

select FirstName & " " & LastName as WholeName from TableA

If you want to combine these two fields from two tables (without a relation) you will get

select A.FirstName & " " & B.LastName from TableA A, TableB B

because the tables don't use a relationship, the resulting records are a cartesian product of the two tables.

If the relationship is used between the 2 tables you will get the following query.

select A.FirstName & " " & B.LastName from TableA A inner join TableB B on A.Id = B.Id

If you want to insert the resulting recordset into a tables use the insert command.

HTH

 

[SyafiqIzzat]

Thank you for the reply but I think you got my question wrong. Actually it is not that I want to combine eg. First Name & Last Name in one field.
Referring to example in my first post,

I have two tables with same field names. Let say both tables have fields of Name and Product. In field of Product of the first table, it contains Book and in field of another table, it contains Pencil.

I want the output to be in a new table ( New table also got Product field) and I don't want the output to be Book Pencil. I want Book to be in the first row of Product field and Pencil on the second row.

I would conclude that I want to combine two data from different tables to be in a new table, and the data should be in new rows. I hope this time it is more clear.

 

[SyafiqIzzat]

I think I have found the solution to my problem.

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services;

UNION ALL works perfectly in my case as I want to combine the fields into one by different rows.

 

[Guus2005]

As i said, your question was not very clear.

You might want to consider using UNION instead of UNION ALL. UNION is faster and the chance of having duplicate records are very slim...

HTH