Compare 2 tables in Ms Access

My problem is:

I've original version of a table called PMM (Product Material Master). Thro' a web interface, user can change that table contents. Once changed, i need to raise an ECN (Engineering CHange Note) specifying what changes happened to original PMM table whether rows are deleted, new rows are added or existing rows are modified etc. I've both old and new version of PMM tables.

The difference between two PMM tables are captured in a third table called ECN and it has both original & new PMM table entries which are not same.


PMM Table structure is as follows.

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 (original version of PMM table)

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

table 2 (Modified version of PMM table)

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


ECN Table structure is as follows:

old_SbPartNo char(50)
old_PartDesc char(200)
old_manPartNo char(200)
old_manufacturer char(100)
old_vendor char(100)
old_refDesi char(200)
old_qty char(5)
new_SbPartNo char(50)
new_PartDesc char(200)
new_manPartNo char(200)
new_manufacturer char(100)
new_vendor char(100)
new_refDesi char(200)
new_qty char(5)

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

1111 partDesc1 111111 xyz1 vendor1 refdesi1 2 null null null null null null null (null for new entries b'cozthis row is deleted in new version ofPMM table)

3333 partDesc3 333333 xyz3 vendor3 refdesi3 6 3333 partDesc3 343434 xyz3 vendor3 refdesi3 6 (this row has entries for both old & new fields b'coz this row is modified from original one)

null null null null null null null 4444 partDesc4 444444 xyz4 vendor4 refDesi4 8 (this row has old entries as null, bcoz this is a new row of data that is being added to original PMM table)

The second row of data in original PMM table is not changed. So, we won't store that in ECN.

I won't mind implementing this logic in multiple queries. If possible, pls give the complete syntax for the entire query as i'm not much conversant with sql. This is very urgent as i need to meet the deadline shortly.

Thanks in advance.