Very simple Q i'm sure...!! Joining similar fields into one

[tom1978]

Hello,

I have three tables, in each table I have results for chemical tests (lets say phosphate, ammonia and dissolved oxygen).

One table contains the analytical results from water samples taken from a lake, another from water samples taken from a river, and the third from an estuary. Each sample has a unique ID number, and there are 10 samples for each water category.



So each table has a field for the ID number, then three further columns for each of the chemical substances.

I want to create a single table which will have 30 records, with one column for all of the ID numbers, and then the three further columns for the results of the chemical tests.

At the moment I am joining them together in excel, by copying one below the other, and then having to manually 'shift' (copy + paste) the results from the chemical tests into single columns.

I'm sure there is a simple way to do this in Access! Any help greatly appreciated.

Thanks
Tom

[recyan]

Have you checked out if UNION queries are of any help?

Thanks

[jwill]

I agree you could try a union. Got this off of another site.

select * into table3
from (select * from table1 union all select * table from table2) as uniontable;

remember if the new table (represented by "table3") has already been created, it will be deleted first. Proceed w/ caution .