Merging two tables with identical fields

[Don]

Hi,
I have two tables with identical fields (i.e. ID, OtherHerb1,
OtherHerb2, OtherHerb3, OtherHerb4). In one table there are 744
records. In the other there are 651 records. Some are complete in one
and some are complete in the other. But, there is no consistency in the
missing data.

I would like to merge these two tables so that the ID field is unique
and the other fields are updated to reflect all data from both tables.

Any suggestions?

I so appreciate any advice.

Thanks in advance.

 

[Jerry Whittle]

Hi Don,

Are some records completely missing or do you have some where something like
OtherHerb1, OtherHerb2, OtherHerb3 are filled in but OtherHerb4 isn't?

If there are records in both tables having the same ID, but the data in the
record is different, which table takes precidence?

 

[Don]

I do have some records that exist in one table and not the other. I
also have records where your example is represented.

I just made a single table out of both tables with duplicates. I then
ran a query against that table using DISTINCT in the ID field. It looks
like I now have a table where all ID's are represented with all
associated data. I've only spotchecked it so far. Does this seem like
it could work?

Thanks
Don

 

[John Spencer]

I would check to see what happens when the same ID has different values in
the "Herb" fields. For example, if you had

ID OtherHerb1 OtherHerb2
1 Basil Rosemary
1 Rosemary <Null>

what do you want to happen?