MySQL Forums
Forum List  »  Microsoft Access

Comparing two Ms Access tables
Posted by: Laxmi
Date: March 15, 2005 12:00AM

I've two Ms Access tables with the same structure.

Tablestructure is as follows:

Table table1
SbPartNo char(50)
PartDesc char(200)
manPartNo char(200)
manufacturer char(100)
vendor char(100)
refDesi char(200)
qty char(5)

My requirement is as follows:

table 1

1111 partDesc1 111111 xyz1 vendor1 refdesi1 2
2222 partDesc2 222222 xyz2 vendor2 refdesi2 4
3333 partDesc3 333333 xyz3 vendor3 refdesi3 6

table 2

2222 partDesc2 222222 xyz2 vendor2 refdesi2 4
3333 partDesc3 343434 xyz3 vendor3 refdesi3 6
4444 partDesc4 444444 xyz4 vendor4 refDesi4 8


Table 3 structure is:

oldSbPartNo char(50)
oldPartDesc char(200)
oldmanPartNo char(200)
oldmanufacturer char(100)
oldvendor char(100)
oldrefDesi char(200)
oldqty char(5)
newSbPartNo char(50)
newPartDesc char(200)
newmanPartNo char(200)
newmanufacturer char(100)
newvendor char(100)
newrefDesi char(200)
newqty char(5)

After comparing the above 2 tables, i want the result to be stored in third table as follows.

1111 partDesc1 111111 xyz1 vendor1 refdesi1 2 null null null null null null null
3333 partDesc3 333333 xyz3 vendor3 refdesi3 6 3333 partDesc3 343434 xyz3 vendor3 refdesi3 6
null null null null null null null 4444 partDesc4 444444 xyz4 vendor4 refDesi4 8


Here is we observe, 1st row in table 1 not present in table2. 2nd row in table1 is modified in table2. 3rd row in table 2 is not present in table1. So, after comparing table 1 and table2, the differences are captured as old and new. Old referes to table1 data and new referes to table2 data.

Is itpossible to do this in Ms Access? If so, how.

I appreciate if anyone can provide me the sql syntax for it. I'm using this for a web application. So, pure SQL is needed.

Options: ReplyQuote


Subject
Views
Written By
Posted
Comparing two Ms Access tables
2499
March 15, 2005 12:00AM
1950
March 02, 2006 10:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.