Need Help with Comparing Tables!!!

[Guest]

I had posted regarding this problem. Have not had a response that works.
Last response almost worked except it was not overwriting correctly. Thought
I would post again. I have two identical tables. Same fields same data.
Table A and Table B have to be compared. However, if there are differences
Table B's needs to overwrite Table A. That is all. There maybe nulls those
just need to be ignored. Can someone please help. There are over 3500 rows
that have to be looked at. Thank you very much. I would appreciate
something simple.

 

[Michel Walsh]

How do you detect that a row in tableB is matching a row in tableA, but
where fields are different?


Assuming you have a table Inventory (ItemID, UnitPrice) and a table of
NewPrices (ItemID, UnitPrice) where this last one have to write over the
UnitPrice in table Inventory, and even, add not existing items in inventory,
but now present in NewPrices (brand new item that arrive in the inventory):


UPDATE Inventory RIGHT JOIN NewPrices
ON Inventory.ItemID=NewPrices.ItemID
SET Inventory.ItemID = NewPrices.ItemID,
Inventory.UnitPrice = NewPrices.UnitPrice


should do the job. Works only with Jet, not with MS SQL Server.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks Michel for responding. I am working with Access 2003 not sure diff
between jet and ms sql server. I do not need to add. I just need to
identify the rows that are not the same and then overwrite with Table B's
data. My data consists of phone numbers and address fields.

 

[Michel Walsh]

If you are using a file with an mdb extension, you are probably using Jet
(unless you LINKED your tables).

So, assuming your phone numbers are the 'key' and that address are to be
update if they do not match, for the same phone number....


UPDATE TableA RIGHT JOIN TableB
ON TableA.PhoneNumber=TableB.PhoneNumber
SET TableA.PhoneNumber= TableB.PhoneNumber,
TableA.Address= TableB.Address



should do.


Hoping it may help,
Vanderghast, Access MVP