How to Merge two different layout Databases

[tjc240e]

What I want to do is Merge two tables based on an Id Number. All my families are assigned a number. I have two seperate tables and I know that some people exist in the table 2 that do not in table 1. The main problem is that table 2 does not have the same fields as table 1.

Table 1 has id number, last name, first name, middle name, street addr, city, state, zip
Table 2 has id number, full name, full address

Can anyone think of a simple way of merging these two tables together? Or maybe even a complex way of doing it.

Thanks

 

[Juris]

Hi!
Try to use GetWordCount() and GetWordNum().
First of all You need to select records which ID in table 2 cannot be found in table 1 and then by using mentioned functions fill table 1.

Juri Shutenko
Municipality of Maardu, Estonia

http://kodu.neti.ee/~juri4

http://www.hot.ee/jurisfox

 

[mkrausnick]

In order to match records from two tables, there must be a common field. You mentioned an ID. If the ID exists in both tables, then you can use a JOIN. You would get a merged table that contains all the fields from both source tables.

Here's the general syntax:

SELECT TABLE1.*, TABLE2.* FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.IDFIELD=TABLE2.IDFIELD

The purpose of 'FULL OUTER' is to include records from either table that don't have matching records in the other table.

Mike Krausnick
Dublin, California

 

[mkrausnick]

I omitted a caveat in my prior post. My suggestion assumes that each source table contain no more than one matching record in the other table. If the relationship is more like a parent-child, for example where there are many records in TABLE2 that match the same record in TABLE1, then your merged tables will contain records with duplicate IDs.

Also, if you use the example and don't specify the fields explicitly, you will have duplicate occurances of all fields that exist in both your source tables. For example if you have a LASTNAME field in both source tables, the merged table will contain two fields: LASTNAME_A and LASTNAME_B. You can avoid this by specifying in the SELECT which fields you want from which source tables.




Mike Krausnick
Dublin, California