how can I unite 4 tables in one ?

[Guest]

Please someone can tell me what should I use to unite 4 tebles in one. Thank
you

 

[Douglas J. Steele]

A little more details about what you're trying to do would help...

If you've got 4 tables with the exact same columns, and you want to have a
resultant set that includes all rows from Table1, all rows from Table2 and
so on, you'd use a Union query:

SELECT Field1, Field2, ... FROM Table1
UNION
SELECT Field1, Field2, ... FROM Table2
UNION
SELECT Field1, Field2, ... FROM Table3
UNION
SELECT Field1, Field2, ... FROM Table4

On the other hand, if you've got data related to the same entity spread
across 4 separate tables, you'd Join the 4 tables together in a query.

 

[Guest]

Be carefull about unions. Sometimes they multiply records.

Cris you have to specify if the tables are always equal and you just want to
merge the records or the four tables are diferente and just want to make a
query?

Marco.

 

[Douglas J. Steele]

I don't believe it's possible for a UNION query to multiply records. A UNION
query can, however, subtract records. If you've got identical records in two
(or more) of the tables, UNION will eliminate the duplicates. To prevent
this from happening, use UNION ALL

Something I neglected to mention earlier was that should it be desirable to
know from which table a given record came, you can add an extra field to the
UNION query to indicate the source:

SELECT "Table1" AS Source, Field1, Field2, ... FROM Table1
UNION
SELECT "Table2" AS Source, Field1, Field2, ... FROM Table2
UNION
SELECT "Table3" AS Source, Field1, Field2, ... FROM Table3
UNION
SELECT "Table4" AS Source, Field1, Field2, ... FROM Table4

Doing that means that there cannot be duplicates among the subqueries
(unless, of course, the original tables contain duplicates), so no records
will be eliminated as duplicates.