merging three tables with into another with sql

[Big X]

I have already achieved this in access and was trying with straight SQL earlier I would just like to know what I'm doing wrong in sql or what syntax I'm missing. I have three tables with identical data that was created from .txt delimited files I have received. I tried the following code.

SELECT VIN, OtherID, Rego, DriverName, OwnerName, VehicleDescription, ExpiryDate, Address, City, Postcode, State INTO Iload
FROM STDhy60, STDhy59, STDhy55
WHERE VehicleDescription like '*iLOAD*';

So basically I am trying to get the records from three tables that contain iload as a value into a new table. In the end I just imported the information into a table called STD then imported the other two .txt files into that table using access. Then wrote an sql statement to get the information I needed out of one table which works.

SELECT VIN, OtherID, Rego, DriverName, OwnerName, VehicleDescription, ExpiryDate, Address, City, Postcode, State INTO Iload
FROM STD
WHERE VehicleDescription like '*iLOAD*';

The first code basically causes an error, this field may exist in more than one table. I not comparing values though I just want all the records which are unique into another table using SQL statement similiar to the first bit of code.

[Stewart Ross]

Hi. There are two problems with the first SQL statement you list. The first is the use of non-unique field names, and the second is that there are no joins between the tables.

When the same field name is present in more than one of the tables selected you need to qualify the field name with the name of the table you are selecting it from, throughout the SQL:

tablename.field name

or where there are spaces in the names

[name of table].[name of field]

If you look at the SQL that Access creates (View, SQL View) from the graphical query editor you will see that Access itself always creates fully qualified field references throughout, and you may well have to do the same throughout yours.

There were no INNER JOINs in your first SQL statement, and this will mean that you end up multiplying all rows of the first table by the rows of the second and third tables. This gives what is known as the Cartesian Product of the tables. If there were 100 rows in the first, 200 in the second and 300 in the third you will end up with 100x200x300 rows, 6,000,000 rows in the product table. Cartesian products grow very rapidly. You would need to define the common field or fields joining the tables to avoid this happening, using an appropriate INNER JOIN statement.

Given that you have three separate tables the JOIN approach is fundamentally wrong, as the tables do not contain rows in common (as far as I can tell from what you have told us).

It would be better in my opinion to have three separate append queries, one for each table, then create a macro to run all three one after the other as one operation.

-Stewart