VBA Code To COMPARE TWO TABLES

[osimini1]

I want to use VBA to compare two tables and write out the field where there is no match. "Table A" is the main table and "Table B" is the varying table. Meaning to compare "Table B - fields" against "Table A - fields" to produce no match report. The reason that I want to use vba is because it is the best option. Can someone point or guide me on how to accomplish this. I will appreciate any help. Thank you very much in advance.

Here are the tables
Table A (Base Table)
FIELD
PROVIDER_ID
LICENSE_NO
LAST_NAME
FIRST_NAME
ETHNICITY
GENDER
DOB
SSN
TIN
LICENSE_STATUS
LIC_EXPIRATION_DATE
PROFESSIONAL_DEGREE
DEA_NO
STATUS



Table B (Varying Table)
FIELD
PROVIDER_ID
LICENSE_NO
LAST_NAME
FIRST_NAME
ETHNICITY
GENDER
DOB
SSN
TIN
LICENSE_STATUS
LIC_EXPIRATION_DATE
PROFESSIONAL_DEGREE
DEA
STATUS

 

[CaptainD]

What are you using as a criteria to find what is "Not IN" table B ?

Seems to me a simple query would be your best method, not VBA

You could use a left join or a "Not In" type query to show you the records not in table B

 

[dhookom]

I have done this before by creating a couple union queries and then comparing them in another query. This allows you to output each field and the values if they are different.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Why not simply use the Unmatched Query Wizard?


Randy

 

[dhookom]

write out the field where there is no match

I don't think the unmatched query wizard will provide any details regarding which fields don't match.

Duane
Hook'D on Access
MS Access MVP

 

[jedraw]

Osimini1,

I'm not sure what you are really asking.
Do you want to compare table structures, or do you want to compare values?

 

[osimini1]

Thank you for all your help. "ACTUALLY, I WANT TO COMPARE FIELD VALUES IN THE TABLE. FOR EXAMPLE, LAST_NANE (TABLE B) AGAINST LAST_NAME (TABLE A). But nt comparing the tables structure. can some please help. I

 

[dhookom]

Did you understand my reply. It's exactly what I have done in the past and allows identification of individual field values that are different.

Can we assume Provider_ID is the primary key in both tables?


Duane
Hook'D on Access
MS Access MVP

 

[randy700]

SELECT * FROM TableA 
INNER JOIN TableB 
ON TableA.ProviderID = TableB.ProviderID
WHERE TableA.LastName <> TableB.LastName

Randy

 

[dhookom]

Randy700,
Your solution works for one field not all thirteen.

SELECT PROVIDER_ID, "Last_Name" as TheField, TableA.Last_Name As AValue, TableB.Last_Name as BValue
FROM TableA 
INNER JOIN TableB 
ON TableA.ProviderID = TableB.ProviderID
WHERE TableA.Last_Name <> TableB.Last_Name
UNION ALL
SELECT PROVIDER_ID, "First_Name", TableA.First_Name, TableB.First_Name
FROM TableA 
INNER JOIN TableB 
ON TableA.ProviderID = TableB.ProviderID
WHERE TableA.First_Name <> TableB.First_Name
UNION ALL
SELECT PROVIDER_ID, "LICENSE_NO", TableA.LICENSE_NO, TableB.LICENSE_NO
FROM TableA 
INNER JOIN TableB 
ON TableA.ProviderID = TableB.ProviderID
WHERE TableA.LICENSE_NO <> TableB.LICENSE_NO
UNION ALL
-- etc --
SELECT PROVIDER_ID, "STATUS", TableA.STATUS, TableB.STATUS
FROM TableA 
INNER JOIN TableB 
ON TableA.ProviderID = TableB.ProviderID
WHERE TableA.STATUS <> TableB.STATUS;

If there is the possibility of Null values, each WHERE clause might need to use Nz() in order to compare the values.



Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Duane,
I thought if I gave him a start, he could figure out how to complete it. I'm not going to write all of his code for him.


Randy

 

[dhookom]

Since I had already suggested the method and got a "can some please help" reply, I expect osimini1 needed a little more assistance.

Duane
Hook'D on Access
MS Access MVP

 

[osimini1]

lET ME USE THIS OPPORTUNITY TO THANK EACH AND EVERY ONE OF YOU FOR YOUR HELP.dhookom, I used your method or suggestion and it worked.

Againt thank you all.