By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,187 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

merging three tables with into another with sql

P: 42
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT VIN, OtherID, Rego, DriverName, OwnerName, VehicleDescription, ExpiryDate, Address, City, Postcode, State INTO Iload
  2. FROM STDhy60, STDhy59, STDhy55
  3. 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT VIN, OtherID, Rego, DriverName, OwnerName, VehicleDescription, ExpiryDate, Address, City, Postcode, State INTO Iload
  2. FROM STD
  3. 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.
Apr 28 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
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.fieldname

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
Apr 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.