combining tables 1

[wdbouk]

I have 3 tables that have a common field . I want to combine these 3 tables into one . i.e I want to include the unmatched record and the matched records as well.How can I do so?
Best wishes
wdbouk

 

[willir]

wdbouk
You mean you want to append the tables into one?

You can use the APPEND query to achieve this. Use the query builder, but first you have to decide if you are going to append to an existing table, or append to a new table. If you are going to use a new table - create it.

Then with the query builder
- add one of the tables you intend to use as a source (not a target)
- change the query from a regular SQL query to an Append query -- from the Menu, select "Query" -> "Append"
- select the target table
- match the fields from the source table to the target table
- run the query ("!" from menu)
- repeat with other source tables

Or do you mean you want to "see" the results of the three tables but not update / append the tables?

Use a UNION Query. Again use the query builder, but now the graphical interface will not be available -- you will have to type the query in by hand. See the Access help on UNION query. Basically, the syntax is...
[tt]
SELECT field1, field2 ...
FROM Table1
UNION
SELECT field1, field2 ...
FROM Table2
UNION
SELECT field1, field2 ...
FROM table2
;
[/tt]

The "gotcha" with a UNION query is that you are very limited in the fields you can retrieve from the different tables - they have to match data types.

Richard

 

[Darrylles]

Hi,

To be safe - from your description, you would be better to list your table-fields.

At least back your table up before applying the above query.

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk