Merging tables

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have two tables with two columns each. Each table has an id column and a
value column. I would like the two tables to be merged so there are three
columns id, valuefromtable1 and valuefromtable2 and in such as way that all
records from both tables are included but where they don't match the
corresponding valuefromtable1 or valuefromtable2 is empty. How can I go
about doing this?

Thanks

Regards
 
John said:
Hi

I have two tables with two columns each. Each table has an id column
and a value column. I would like the two tables to be merged so there
are three columns id, valuefromtable1 and valuefromtable2 and in such
as way that all records from both tables are included but where they
don't match the corresponding valuefromtable1 or valuefromtable2 is
empty. How can I go about doing this?

Thanks

Regards
Click to expand...

How are you planning to match the two tables? Tables don't really have
an order like a spreadsheet. They are more like buckets.

Are the ID's unique within each table?
 
Hi John

I would do it in a query, which you "could" use to make a new table if you
wanted but not really sure what you want to do with the data so it's up to
you.

Create a query and bring in Table1 and Table2 into the gride. Use this (air
code so may need a bit of work but "should" be OK)

SELECT
IIf([Table1]![valuefromtable1]=[Table2]![valuefromtable2],[Table1]![valuefromtable1])
AS Combined,
IIf([Table1]![valuefromtable1]<>[Table2]![valuefromtable2],[Table1]![valuefromtable1])
AS ValueFromTable1,
IIf([Table1]![valuefromtable1]<>[Table2]![valuefromtable2],[Table2]![valuefromtable2]) AS ValueFromTable2 FROM Table1, Table2;

I have assumed the the field and tables names are
Table1 and valuefromtable1
Table2 and valuefromtable2

Of course you will need to change these to what they really are.

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :-)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.
 
Well id is what needs to be matched some will match some wont. Matching ids
will have both values non matching ids will have only one value. Example
below.

Thanks

Regards


Example

T1-ID T1-VALUE
1 A
2 B

T2-ID T2-VALUE
2 C
3 D

Expected result after merge

ID VALUE1 VALUE 2
1 A <empty>
2 B C
3 <empty> D
 
Hi John

Rename Table1 as "NewTable" (or, if you want to keep the original tables as
they are then make a copy of Table1).

Add a new field to NewTable for "ValueFromTable2".

Now create an update query with NewTable and Table2 joined by the ID, and
set NewTable.ValueFromTable2=Table2.ValueField. This will add the Table2
values for the IDs that occur in both tables.

Finally, create an append query (append to NewTable) based on Table2 with an
outer join to NewTable ("include all records from Table2 and only those
matching records from NewTable"). Add criteria to select only those records
where Newtable.ID is Null (unmatched records). Select Table2.ID and
Table2.ValueField to populate NewTable.ID and NewTable.ValueFromTable2
respectively. This will add any records from Table2 which did not have ID
values in Table1.
 
Assumption: ID is a unique within a table (that is it could be the primary key)

SELECT T1.ID, T1.Value, T2.Value
FROM Table1 as T1 LEFT JOIN Table2 as T2
ON T1.ID = T2.ID
UNION
SELECT T2.ID, T1.VALUE, T2.Value
FROM Table2 as T2 LEFT JOIN Table1 as T1
 
Back
Top