Compare two tables in VBA and insert value

Question

I want to compare data from two different databases and insert missing value in original database.

In tableA I have Name, Surname and Address and in other table, tableB I have Name, Surname, Address, Phone, Zip, Title, Unique code. I need to compare name, surname and address from tableA with tableB, and if I found a match than I need to add one field in tableA and store that field value against that particular record.

To clarify, if I am trying to match "John","Smith","23 May Road" from tableA with tableB and found a match, than I need to update John Smith record from tableA with added field Title,Phone,Zip and unique code. I tried with inner join but no luck and spent almost a day with different combinations.

Someone who has better grip on join please help me.

Regards..

This looks a lot like a SQL question. Is your problem with the SQL (in which case, tag the question [SQL]) or with the VBA around it - and if the latter why are you using VBA?! — aucuparia
I am using VBA because I am creating small application in Access — katya.lee

Dkellygb Dkellygb · Accepted Answer · 2016-10-20T07:19:03

You can do this without using any code/VBA. Just construct an appropriate query.

Follow these steps:

Create a new query and add both tables
Drag the fields you wish to match from tableA onto there corresponding fields in tableB
Change the query type to an update query under the "Query" menu
Add the fields you need to update from tableB (NOT tableA) to the data rows by dragging down from the table
In the "Update To:" row, type the fieldnames from tableA which has the data you wish to use to update tableB. You need to include the filenames. For example, under phone, type "tableA.phone". Access will change it to "[TableA].[phone].
Execute the query by selecting Query|Run from the menu.

If you are interested, here is the Access SQL generated:

UPDATE tableA 
LEFT JOIN tableB ON (tableA.Address = tableB.Address) AND 
(tableA.Surname = tableB.Surname) AND (tableA.Name = tableB.Name) 
SET tableB.Phone = [tableA].[phone], 
tableB.Zip = [tableA].[zip], 
tableB.Title = [tableA].[title], 
tableB.UniqueCode = [tableA].[uniqueCode];

Of course there are VBA solutions you can use but this seems like the most straightforward approach.

Compare two tables in VBA and insert value

1 Answers