merge 2 tables to one

A

AshleyS

How do i merge 2 tables into 1?
Ie merging the values from table A and B into table C but still keeping the
values in A and B (and keep the tables seperate)?

So on update of say record X in table A it will automatically update the
value change in table C.

I have tried a table make query but the values end up repeating and are
linking to other values unrelated to them.

Many thanks
Ashley
 
C

Chaim

You want to create a view based on the two tables. The SQL would read
something like:
create view tblC as <select statement that gets you the records you want
as you want them from tables A and B>

Every time you update the underlying tables and then select * from tblC",
the view is 'recreated' and you'll see the changed data.

You don't give enough details to help with the query. Sorry.
 
J

John Vinson

How do i merge 2 tables into 1?
Ie merging the values from table A and B into table C but still keeping the
values in A and B (and keep the tables seperate)?

So on update of say record X in table A it will automatically update the
value change in table C.

I have tried a table make query but the values end up repeating and are
linking to other values unrelated to them.

Many thanks
Ashley
Click to expand...

Well, you should certainly NOT attempt to store the data redundantly
in the third table, especially not if you expect an update to TableA
to automagically change the value in TableC. Tables simply don't work
that way!

I think what you need instead is, first, consider a UNION query to
"string together" the contents of TableA and TableB:

SELECT field, field, field, field FROM TableA
UNION ALL
SELECT field, field, field, field FROM TableB;

The number and datatypes of the SELECT clauses must match; the field
names don't need to. This query will give you a recordset that looks
and acts like a table, and can be used as the recordsource for a form
or report; however it will not allow updating. It will, however,
reflect any updates made to TableA or TableB.

The second thing to consider is that if you have two tables of
identical structure which you often need to combine - maybe you
shouldn't HAVE two tables. Can these be combined into one table,
perhaps with an additional field to distinguish the data in A from the
data in B?

John W. Vinson[MVP]
 
A

AshleyS

Thanks for the help
The Union Query allowed me to get the layout that i required.

Thanks again
 
Top