Little_Anj
Registered User.
- Local time
- Tomorrow, 04:04
- Joined
- Dec 14, 2012
- Messages
- 25
I am trying to work out a way of partially merging two records. I do not actually want to delete one, just mark as "inactive" and reassign account numbers (& orders which are matched by account number not customer pk)
I have a table of customers with a yes/no field of Active/Inactive, and a merged/unmerged yes/no field
A table of account numbers (with customer pk)
A table of merged customers (merged customer pk, date merged, & memo)
This will all be done from a form of sorts (with a query table view to show relevant records) (this I can do)
Select the two records to be merged (maybe a check box) (this I can work out)
A button to activate the merge (this I can do)
The next few steps I am stuck..
Upon clicking the button, a pop up box (with two options, radio) to select which customer will now be the primary customer
After selecting the primary..
The secondary to be marked in the customer table as Inactive & Merged (yes)
In the account numbers table for all instances of the customer pk for the secondary to be changed to the "Primary customer pk"
In the merged table, today's date with the memo to show the now primary customer pk, & old account numbers
I do not want the secondary customer to actually be deleted, rather it's pk where referenced to be changed.
The reason for all of this, is traceability. (and if it needs to be reversed, or just corrected it is easily manually done by changing the customer pk for the account numbers)
Customers are being merged due to duplicate entries OR one customer buying out another. (In my line of work this happens every month).
All of my data is imported, so I cannot stop duplicates. It is checked after importing data. (another post for another day)
I don't expect a complete solution, just some pointers
I have a table of customers with a yes/no field of Active/Inactive, and a merged/unmerged yes/no field
A table of account numbers (with customer pk)
A table of merged customers (merged customer pk, date merged, & memo)
This will all be done from a form of sorts (with a query table view to show relevant records) (this I can do)
Select the two records to be merged (maybe a check box) (this I can work out)
A button to activate the merge (this I can do)
The next few steps I am stuck..
Upon clicking the button, a pop up box (with two options, radio) to select which customer will now be the primary customer
After selecting the primary..
The secondary to be marked in the customer table as Inactive & Merged (yes)
In the account numbers table for all instances of the customer pk for the secondary to be changed to the "Primary customer pk"
In the merged table, today's date with the memo to show the now primary customer pk, & old account numbers
I do not want the secondary customer to actually be deleted, rather it's pk where referenced to be changed.
The reason for all of this, is traceability. (and if it needs to be reversed, or just corrected it is easily manually done by changing the customer pk for the account numbers)
Customers are being merged due to duplicate entries OR one customer buying out another. (In my line of work this happens every month).
All of my data is imported, so I cannot stop duplicates. It is checked after importing data. (another post for another day)
I don't expect a complete solution, just some pointers