A query that joins 2 tables

J

J. Silva

Hello

I’m having some problem to make a query that joins 2 tables.

The two tables (by kind of product) have some identical fields and others
that are different. the identical fields are: name of the company, the kind
of product, reference and the price. My aim is make a query that joined in an
alone one these three fields for afterwards do the respective report, with
this headline: name of the company, kind of product, reference and price. Can
you help me?
 
J

J. Silva

My problem now is that the name of the fields are different in the 2 table
(table 1: namecompany1; product1;....and in table 2: namecompany2;
product2;...)

"J. Silva" escreveu:
 
J

John Vinson

My problem now is that the name of the fields are different in the 2 table
(table 1: namecompany1; product1;....and in table 2: namecompany2;
product2;...)
Click to expand...

That doesn't matter for a UNION query. The number and data types of
the fields must correspond but the names can be anything.

SELECT namecompany1, product1 FROM table1
UNION
SELECT namecompany2, product2 FROM table2

or for that matter

SELECT CompanyName, ProductTitle FROM Table1
UNION
SELECT NombreDeCompania, Producto FROM Table2

John W. Vinson[MVP]
 
J

J. Silva

Thank very much. It works!!
But I have a field in the result of the query (that is a memo field in the
original table) that is cut in the middle. Why? How can I fix it?
Thank you again!
Joana.

"John Vinson" escreveu:
 
I

Ian Chappel

If you're not up to speed on SQL, I would suggest creating the individual
halves of the query in the Design Grid, and then copying the text from the
SQL view of each and pasting either side of the "UNION" operator. Make sure
you don't leave a semi-colon within the expression (Access usually creates
it at the end of the SQL statement). Also, only put one ORDER BY statement
at the end of the whole string, if it's required.
 
J

John Vinson

And can I union more than one table?
Click to expand...

Dozens:

SELECT this, that, theother FROM TableA
UNION
SELECT who, what, which FROM TableB
UNION
SELECT eep, ouch, yipe FROM TableC
UNION
<etc etc>

You might want to open Access's Help and search for UNION. It explains
this and other details about the UNION operator.

John W. Vinson[MVP]
 
J

John Vinson

But I have a field in the result of the query (that is a memo field in the
original table) that is cut in the middle. Why? How can I fix it?
Click to expand...

Change all the UNION operators to UNION ALL.

UNION removes duplicates before presenting the records; UNION ALL
shows all the records, duplicates included. Access must truncate MEMO
fields to remove duplicates (since a memo can be enormous, it's
considered too expensive to compare two records for identical memo
field values).

John W. Vinson[MVP]
 
Top