In Access 2000, I have two tables that need to be reconciled.
One is an imported table called "Orders" (which cannot be changed):
OrderNumber | Customer
A | AAA
B | BBB
C | CCC
D | DDD
E | EEE
F | FFF
G | GGG
The other is a table internal to the Access project called "tblOrderStatus "
OrderNumber | Status
B | abc
D | def
G | ghi
J | jkl
L | mno
In my frmStatusUpdate form, at Form_Load(), I would like to compare the two tables, remove any records from tblOrderStatus that do not share an OrderNumber with the "Orders" table, and add any records that do not exist already. The desired output after the Form_Load() would be:
tblOrderStatus:
Order Number | Status
A |
B | abc
C |
D | def
E |
F |
G | ghi
If this is not possible at Form_Load(), then any other method of reaching this result will suffice.
I have tried doing this through a query with the join property to include ALL "Orders", and matching "tblOrderStatus ", but this does not allow me to add records to tblOrderStatus to account for new additions to the "Orders" table.
Thank you for the help!
One is an imported table called "Orders" (which cannot be changed):
OrderNumber | Customer
A | AAA
B | BBB
C | CCC
D | DDD
E | EEE
F | FFF
G | GGG
The other is a table internal to the Access project called "tblOrderStatus "
OrderNumber | Status
B | abc
D | def
G | ghi
J | jkl
L | mno
In my frmStatusUpdate form, at Form_Load(), I would like to compare the two tables, remove any records from tblOrderStatus that do not share an OrderNumber with the "Orders" table, and add any records that do not exist already. The desired output after the Form_Load() would be:
tblOrderStatus:
Order Number | Status
A |
B | abc
C |
D | def
E |
F |
G | ghi
If this is not possible at Form_Load(), then any other method of reaching this result will suffice.
I have tried doing this through a query with the join property to include ALL "Orders", and matching "tblOrderStatus ", but this does not allow me to add records to tblOrderStatus to account for new additions to the "Orders" table.
Thank you for the help!
Comment