Merging two records- keeping tracability (1 Viewer)

[Little_Anj]

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

 

[jdraw]

Don't know if you're still working on this, but have you created a data model of the things you are working with and how they relate?

That is a usual starting point to get all the business facts organized and tested to some degree before getting into the nitty-gritty parts of Access.

Traceability would be needed on things like company mergers (airlines, insurance companies,..)
Often in databases records are not physically deleted. They are often logically deleted (also called shadow delete). A boolean field/flag is associated with the record. The flag is turned on signifying a delete. You always have access to the old records...just in case.

 

[Solo712]

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

Hi Little_Anj,
as jdraw points out it does pay to plan things ahead of actually asking for design tips for a specific application. There are a few things at first glance which do not add up in your description of the account merging tasks. They represent issues that need to be resolved first.
1) Mergers as a result of buyouts are surely not 'mergers' of accounts duplicated by error. These two situations, though having similar data management effects are in fact very different. One represents a transformation of business entities having assets with, or claims against, your firm. The other is just faulty record-keeping. There is no need to keep trace of 'Buttle' when in fact the customer's name is 'Tuttle'. It suffice that Mr Buttle's transactions are transferred to the account of Mr Tuttle and Mr Buttle's account marked as deleted. (I am not considering here the issues of notification of the actual account holder. They could be really dicey.) 2) Is a separate table of 'merged' customers justified ? I don't know but if the only reason is to keep record of mergers, acquisitions and/or divestments then the answer is definitely, 'no'. A table recording changes in the account ownership and transfer of balances should most likely suffice. 3) The idea of splitting the accounts back becomes questionable once transactions are recorded after the merger. Naturally, the firsts questions here are: who inherits the joint credits / debits are when the partners split, and who decides that ? Before taking this to Access designers, I would talk to the company lawyers first.


Best,
Jiri

 

[Little_Anj]

Hi Jiri and Jdraw thank you for your assistance.

Thank you for your assistance.

Jiri, thank you for your good points. I completely agree with them when setting up a sales database!

However, I am not quite doing that.

We sell our products to wholesalers (using an accounting software), then each month we get reports (.csv files) back from our wholesalers on who they have sold too with details of our products.
This is the database I am trying to create.

The main issue is, I am never actually entering sales into the database so normal customer accounts management doesn't quite work.

I am matching the sales to customers based on their account numbers with their wholesalers see my post (Sales Database- All sales are imported from different wholesalers (warehouses)). Sorry I cannot out in a link (yet).

This is the issue hence my question on this post:
I do not know account number in advance, so when the sales data gets imported the "customer" could be duplicated based on their having multiple account numbers, or using different wholesalers.

Every month a few (10-30+) "customers" will need to have merges done because they now have duplicates.
We use the address details as the criteria, given to us by the wholesalers.

Problem 1. The addresses may be slightly different so they do not correctly get matched (merged). One wholesaler might refer to them as "shop 3 at shopping centre A", and another "3/ Street road", with no mention of the shopping centre. So for a while we will have one customer twice!
Problem 2. The "Customers" may be sold, but the store still remains. Our sales rep still visit that store to encourage selling our products. The change in ownership triggers new account numbers with the wholesalers.
Problem 3. A Customer may physically move, in that they take the business name and "buy" another store and now trade from there. So their address details get updated. Now this is one of the reasons I need traceability. Their old location, usually gets bought up by another company (customer) and they start trading from that location.
I now have two different customers. These change of business could be in the same month, or a few months apart.

As a result of this (very annoying way our customer type operates), we incorrectly merge customers on the basis of "they are at the same address", when in fact they have moved, and this truly is a new customer!!:banghead:

So if I find out that customer A moved on 1st March, and customer B started trading from the old address on 2nd March, I can track back to the imports from the wholesalers, and verify which account number belongs to whom.
Therefore moving the sales to the correct customer.

Likewise, if I merge two customers because I assumed they were the same and they are not. I would like to easily see the "inactive" customer and the account numbers they had so I can undo what was done.

I can (figure out how to ) post a screen shot of my relationships so far if that will assist with suggestions.

Thanks again, you are all very helpful.

 

[Solo712]

We sell our products to wholesalers (using an accounting software), then each month we get reports (.csv files) back from our wholesalers on who they have sold too with details of our products.
This is the database I am trying to create.

The main issue is, I am never actually entering sales into the database so normal customer accounts management doesn't quite work.

I am matching the sales to customers based on their account numbers with their wholesalers see my post (Sales Database- All sales are imported from different wholesalers (warehouses)). Sorry I cannot out in a link (yet).

To net it out, these customers you are trying to identify are not really your customers but customers of wholesalers who sell your products.

This is the issue hence my question on this post:
I do not know account number in advance, so when the sales data gets imported the "customer" could be duplicated based on their having multiple account numbers, or using different wholesalers.

I understand the part of a retailer appearing on a list of more than one wholesaler, but I am not sure why they should have more than account with the same wholesaler. This is a simple issue of identifying a business entity. To have a reliable source of information you need to control it upstream. Surely there you have a system to identify a business. (In Canada, we recently we had the HST numbers and Tax revenue numbers merged into one number called Business Number. The number is issued once and stays with an incorporated business as long as it exists. The business can move but it cannot hide . I believe Australia has a similar universal number to identify any business.) If you cannot be sure about the identity of the buyers, it makes little sense of building a database tracking their activities. It will be forever a catch-me-if-you-can. You need a csv import which identifies the customer uniquely. Your company should press the wholesalers to provide such reporting.

Every month a few (10-30+) "customers" will need to have merges done because they now have duplicates.
We use the address details as the criteria, given to us by the wholesalers.

That's just not good enough to build reliable reporting on, I'm afraid.

Problem 1. The addresses may be slightly different so they do not correctly get matched (merged). One wholesaler might refer to them as "shop 3 at shopping centre A", and another "3/ Street road", with no mention of the shopping centre. So for a while we will have one customer twice!
Problem 2. The "Customers" may be sold, but the store still remains. Our sales rep still visit that store to encourage selling our products. The change in ownership triggers new account numbers with the wholesalers.
Problem 3. A Customer may physically move, in that they take the business name and "buy" another store and now trade from there.

This is really confusing. I assume you are a company in which salespeople are assigned an area. They have max awareness of the changes in the outlets that sell your products. They need to provide you with updates on the outlets, which store moved, which is under new management, etc. I have hard time imagining a situation in which a company cannot ID reliably its clientele. If there is such a situation then what needs to change are the business controls not the handling of information anomalies created by the lack of business controls.

So their address details get updated. Now this is one of the reasons I need traceability. Their old location, usually gets bought up by another company (customer) and they start trading from that location.
I now have two different customers. These change of business could be in the same month, or a few months apart.

As a result of this (very annoying way our customer type operates), we incorrectly merge customers on the basis of "they are at the same address", when in fact they have moved, and this truly is a new customer!!:banghead:

So if I find out that customer A moved on 1st March, and customer B started trading from the old address on 2nd March, I can track back to the imports from the wholesalers, and verify which account number belongs to whom.
Therefore moving the sales to the correct customer.

What you describe points to the need of some serious rethinking of the business process and information gathering. Before any kind of database design I'd recommend you:

a) get the whosalers to include the ABN number of their customers on their csv extracts they send you.

b) adopt a reporting process for your sales force, in which they flag any changes in retail clientele (actual or planned) and report it back to HQ.

c) rethink the importance of geography vs store ownership in terms of data beimng gathered. Sure, some owners may be more keen sellers of your products but that may be simply because your products sell well in the area they operate. So, it would be likely worthwhile to establish some sales profiles and models before you decide how to collect and manage data.

Likewise, if I merge two customers because I assumed they were the same and they are not. I would like to easily see the "inactive" customer and the account numbers they had so I can undo what was done.

I can (figure out how to ) post a screen shot of my relationships so far if that will assist with suggestions.

Thanks again, you are all very helpful.

You are quite welcome.

Best,
Jiri

 

[Little_Anj]

Update, I have worked it out and it works!!

I have had to modify my table fields and relationships (just a little)

Customer Table : New Y/N, Merged Y/N, Inactive Y/N
Account Numbers Table: Customer ID, Account No
Merged records table: Merge ID, Customer Merged ID, New Primary Customer ID, DateMerged, User ID
Merged account numbers table (NEW TABLE): Same fields as account number table, with DateMerged Field

Customer Table is related to all tables. (Merged table is only related by Customer merged ID, not New Primary Customer ID)

I have a series of Update, and append queries based on Select queries.

First things first, when db is opened, user must login to create a temp Var, to record and hold the current users ID
I found this from "My new youtube teacher"

I have created a Form that shows only my "New Customers" in single form, with a split form to show "All other customers" using a Select query to Not select the Customer ID in my form.

On the Subform, I have an "On click" command on the Customer ID field to open the Merge Form

The merge form is a series of text fields that are automatically populated from the previous form

Private Sub CustomerT_Click()
DoCmd.OpenForm "SelectPrimaryNewCustomerF"
Forms!SelectPrimaryNewCustomerF!ID2 = Me.CustomerT
Forms!SelectPrimaryNewCustomerF!Name2 = Me.CustomerName
Forms!SelectPrimaryNewCustomerF!New2 = Me.NewCustomer
Forms!SelectPrimaryNewCustomerF!Phone2 = Me.Phone
Forms!SelectPrimaryNewCustomerF!Fax2 = Me.Fax
Forms!SelectPrimaryNewCustomerF!Banner2 = Me.Combo22
Forms!SelectPrimaryNewCustomerF!Address12 = Me.Address1
Forms!SelectPrimaryNewCustomerF!Address22 = Me.Address2
Forms!SelectPrimaryNewCustomerF!Suburb2 = Me.Suburb
Forms!SelectPrimaryNewCustomerF!State2 = Me.State
Forms!SelectPrimaryNewCustomerF!Post2 = Me.Post

Forms!SelectPrimaryNewCustomerF!ID1 = Forms!NewAllCustomerListF!CustomerT
Forms!SelectPrimaryNewCustomerF!Name1 = Forms!NewAllCustomerListF!CustomerName
Forms!SelectPrimaryNewCustomerF!New1 = Forms!NewAllCustomerListF!NewCustomer
Forms!SelectPrimaryNewCustomerF!Phone1 = Forms!NewAllCustomerListF!Phone
Forms!SelectPrimaryNewCustomerF!Fax1 = Forms!NewAllCustomerListF!Fax
Forms!SelectPrimaryNewCustomerF!Banner1 = Forms!NewAllCustomerListF!BannerList
Forms!SelectPrimaryNewCustomerF!address11 = Forms!NewAllCustomerListF!Address1
Forms!SelectPrimaryNewCustomerF!Address21 = Forms!NewAllCustomerListF!Address2
Forms!SelectPrimaryNewCustomerF!Suburb1 = Forms!NewAllCustomerListF!Suburb
Forms!SelectPrimaryNewCustomerF!State1 = Forms!NewAllCustomerListF!State
Forms!SelectPrimaryNewCustomerF!Post1 = Forms!NewAllCustomerListF!Post
End Sub

On this form I have a button for each record that is displayed as
Customer A & Customer B
Upon selecting a Customer, the (non visible) text fields primaryCustomer, SecondaryCustomer and UserID are automaticaly populated depending on which button is clicked

I have a close button (Which takes no action and closes the form), and a save button which will run my update & append queries and also create a new merged customer record
(The Merged records table is not visible)
An append query, searches my account number table for the Secondary (or merged customer) ID, and account number, then appends the serach results to my Merged account number table.
An Update query, markes my Merged(Secondary)Customer record as Merged YES, Inactive YES
Another update query, changes my secondary customer ID to the Primary customerID in the accounts table.

Private Sub AcceptChanges_Click()

DoCmd.SetWarnings False
Forms!SelectPrimaryNewCustomerF!MergedRecordsF!CustomerID = Me.SecondID
Forms!SelectPrimaryNewCustomerF!MergedRecordsF!MergedTo = Me.IDprimary
Forms!SelectPrimaryNewCustomerF!MergedRecordsF!UserID = Me.UserIdChange
DoEvents
DoCmd.OpenQuery "qryCloseMergedRecordUPDATE"
DoEvents
DoCmd.OpenQuery "qryPrimaryMergeAccountNoAPPEND"
DoEvents
DoCmd.OpenQuery "qryPrimaryMergeAccountNoUPDATE"
DoEvents
DoCmd.Close acForm, "SelectPrimaryNewCustomerF"
DoCmd.SetWarnings True

End Sub

That's it. I hope this makes sense for those who are interested!