By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Compare two tables in different databases

P: n/a
I'm using Access 2003 and will have to separate databases with the
exact same tables and table structures.

I need to be able to compare the table from the second database
against the same table in the first database. I need to know if there
is a similar name in the table in the first db and then find out if
all the rest of the fields are the same. If they aren't I need to
update the table in db1 with the values from db2. If there isn't a
record with a similar name then I need to create a new record in db1.

I was just wondering what the best approach to do this would be? Any
help would be appreciated.
Jan 3 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Greetings,

For Updating:

UPDATE tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID and tbl2.Name =
tbl1.Name SET tbl2.fld1 = tbl1.fld1, tbl2.fld2 = tbl1.fld2,
tbl2.fld3=tbl1.fld3

And for missing rows do this:

Insert Into tbl1 t1
Select * From tbl2 t2 Where Not Exists (select * From tbl2 t3 where
t3.ID = t1.ID and t3.Name = t1.Name)

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jan 3 '08 #2

P: n/a
Thanks for the help on this issue. The problem is that some of these
tables have 50 or more fields. I was wondering if there is a way to
create recordsets and step through them and compare values?

I may be off on this one. I'm not sure how to approach it.

On Jan 3, 3:02*pm, Rich P <rpng...@aol.comwrote:
Greetings,

For Updating:

UPDATE tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID and tbl2.Name =
tbl1.Name SET tbl2.fld1 = tbl1.fld1, tbl2.fld2 = tbl1.fld2,
tbl2.fld3=tbl1.fld3

And for missing rows do this:

Insert Into tbl1 t1
Select * From tbl2 t2 Where Not Exists (select * From tbl2 t3 where
t3.ID = t1.ID and t3.Name = t1.Name)

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Jan 4 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.