SQL statement to combine tables

[Daryl]

Hi,

I'm trying to create a SQL statement to combine tables. I'm not sure if I have to do any joining. The tables all contain the same information with the same fields: Command, Description, Input, etc... They are only separated into multiple tables for classification. So I want the SQL statement to combine the tables and sort all the entries.

I currently have this:

SELECT * FROM table1, table2, table3 ORDER BY Command, Description

This works for one table, but with more than one I get the error:

Run-time error 3079
The specified field "Command" could refer to more than one table listed in the FROM clause of your SQL statement.

Thanks for any help.

Daryl

[Gaffer]

Try

SELECT * from tblOne
UNION
SELECT * from tblTwo
UNION
SELECT etc etc etc

Hope this helps

[Daryl]

Thanks for the help. Adding UNION worked great in combining the tables. Unfortunately, I now have a new problem . I'm using a data control in VB to link some text boxes with fields in a dynaset recordset.

When I open 2 or more tables, I cannot update the recordset. I found the following in MSDN:

A dynaset-type Recordset object may not be updatable if: 

- There isn't a unique index on the ODBC or Paradox table or tables.
- The data page is locked by another user.
- The record has changed since you last read it.
- The user doesn't have permission.
- One or more of the tables or fields are read-only.
- The database is opened as read-only.

* The Recordset object was either created from multiple tables without a JOIN statement or the query was too complex.

Looks like that last one is my problem. Anyone know of a workaround or how to incorporate JOIN into the statement properly? I did try this without success. Snapshot wouldn't work either.

My SQL statement currently looks like:

SELECT * FROM EID UNION ALL SELECT * FROM NVS UNION ALL SELECT * FROM Power ORDER BY ID;

Thanks for any help.

Daryl

[paulw]

You can't join the tables unless they have some common value. Your best bet is to create a new table with an extra field "Classification" and then add all the tables to that table in turn.

e.g.
INSERT INTO Classification_Table (Classification)
SELECT "Class 1" AS Class, Table1.*
FROM Table1;

INSERT INTO Classification_Table (Classification)
SELECT "Class 2" AS Class, Table2.*
FROM Table2;

etc.

Cheers,

P.