Duplicate Records, identify but need to keep 1

[misscrf]

I am thinking this might be a new one, but hope that someone is familiar with the problem. We have been give these records for all of these customers. The problem is that the records were not kept well. If someone changed a name or address on their account, they got a new account number.

That means that we have about 2000 records, with unique account numbers, but some of the records are actually for the same person. One person may have 2 or 3 account numbers. Their name or address may be the same in the multiple records.

I set this database up for us to send them a mailing, and keep track of the responses, but now I need to make sure that everyone only gets one mailing, even if they have multiple accounts. To do this, I understand that we will need to go through the database for the duplicates and somehow mark them.

Here is the thing. I do have a nice entry form, where users can click a button to find a customer, and go to that customer to enter in activity. I want to add some functionality to handle this duplicate issue, but not sure how.

What I am thinking is a check box to say they are a duplicate, maybe greying out the record if it is not the "master" record that we will use to mail stuff to them. Then, maybe "something" like tab (as I use tabs with some other subforms) to show the other records that are related to this one. Finally a button to take them to the master record.

My question is HELP!!!! lol. Can someone please help me to understand how to construct this? I am looking at how to mark the records as duplicate and as master if duplicate. Whew! That's a lot to say.

I hope this makes sense. Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Golom]

This may be naive but why don't you just get rid of the duplicates rather than invent schemes to manage them?

It seems to me that if you fix the problem that's causing duplication and then drop and/or combine duplicates into a single record per customer all these issues just go away.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[misscrf]

Because we have to have the record, in case someone wants to refer to the other record. We cannot delete any of the customer records. We have to know that John Doe is under acct#5467 but was at one time under #4567 he might call asking under any of the numbers. We have to have a complete trail.

Sorry for not mentioning that. Thank you for responding.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Golom]

Still hate duplication. My approach would be along the lines of:

[li]Create a new table[/li]
[tt]
tblAccountCodes
AlternateCode(PK) PrimaryCode
123 123
456 123
234 123
666 666
667 666
etc.
[/tt]

[li]Identify the duplicates for each customer and load that table. For example, the customer with Account Code "123" also has accounts "456" and "234" in the above table.[/li]

[li]Delete the account records that are not the Primary Account for each customer. Possibly you'll want to merge data into the primary record before deleting.[/li]

Now, SQL of the form

Select C.*

From tblCustomers C INNER JOIN tblAccountCodes A
     ON C.PrimaryAccount = A.AccountCode

Where C.AlternateCode = '" & [Enter Customer Account] & "'"

Will retrieve the single unique account code for that customer regardless of which of the assigned codes is entered.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

Sorry ... got my aliases reversed

Select C.*

From tblCustomers C INNER JOIN tblAccountCodes A
     ON [COLOR=red]A[/color].PrimaryAccount = [COLOR=red]C[/color].AccountCode

Where C.AlternateCode = '" & [Enter Customer Account] & "'"

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

I really will (eventually) get it right

Select C.*

From tblCustomers C INNER JOIN tblAccountCodes A
     ON [COLOR=red]A[/color].PrimaryAccount = [COLOR=red]C[/color].AccountCode

Where [COLOR=red]A[/color].AlternateCode = '" & [Enter Customer Account] & "'"

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

Haven't had enough coffee yet this morning or what???



les

 

[Golom]

Only on my second pot ... not nearly enough.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[misscrf]

lmao thanks for all the responses. I won't be able to delete the record at all. The look up table is what I was thinking, but here's a catch. A duplicate record (ie the 123 example you used) might have had some activity recorded. If we ever need to be able to look it up we have to have it available.

The point of only have 1 of a customers records is mainly for running mailmerges.

That will be easy with the table you suggest. Here is the tough part that I am trying to do.

On the entry form where users look up a customer and enter activities for that customer I want to add some buttons.

One would say "Has Duplicates" When clicked the user will get a user form tied to the dup table, and can enter in any account number as a duplicate. I figure this will be a spreadsheet form where they can keep entering in more and more duplicates for one master record.

If they find a customer that has been deemed a duplicate of another record, the button would change to "Is a Duplicate" Pressing that button would take them to the master record.

That button will only show for the dup customer record, if the user entered their acct # in from the master acct record.

I have no problem with changing the button on update, on current and the code. I think I can do the form pretty well too. My concern is the table. Will it work for lookups and all if I have the following?:

A dup table which has
1)DupID PK
2)a lookup to the account number from the main tblcustomers - to be called masterdup
3)an identical lookup called dups (or something)

It being a lookup would be helpful for the entry form so that when they type in an account number, they can see the info about that account number for double checking. It can show them the name, address, etc.

Thanks for all of your help.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I figured out where I am really stuck. lol. I got the table and form going. Now when the form opens, it will open from a button on the main entry form. As the user enters in the duplicate account number(s) I need any new entry to fill in with the account number from the main form.

I am so not good at this kind of thing! The hardest part is that when a user hits the button and the form opens it should be ok for the initial record, but it has to fill the same for ever record after that, until the form is closed. Then on open it will be whatever record the main form is on again.

This is exhausting trying to understand what I need to do!

grr @ people who don't keep normalized records, and then force me to deal with them. lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Well, I got this going and I have construct what I think the behavior of the check box should be on the entry form that signifies whether a record is or has duplicates.

(My verbose statement)

If it is in the master, then

The checkbox label will say "Has Duplicates"
The duplicates form/ tab will show and be lit up for the user to see. They can add new duplicates to the list, the default for the master id will be the current id on the main form.

If it is in the dups, then

The checkbox label will say "Is a Dupicate"
A button will be visible to hit, to take them to the master record.
The main information will be greyed out, but they can view that records address and activity tab/forms if they wish.
The dup tab may also be visible for them to see the record it is a dup of, but that will come at the end.

If it is not in either,

The checkbox will be available with label saying "Has Duplicates"
If they check it the form/tab will not visible, and will only become visible if the user checks the box.
-----------------------

The users will be instructed that you choose your duplicates from the master.

------

Here is the code that I wrote:

As of now, when I check the checkbox that a record has duplicates, the checkbox stays on for all records, the formatting doesn't take, etc. Not sure what to do with code to correct it.

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Dim lngRed As Long
Dim lngGray As Long

lngRed = RGB(255, 0, 0)
lngGray = RGB(221, 221, 221)


If DLookup([ID], "tblDuplicates", "[MembPrimAcct] =" & Forms![frmMemberEntry]!VENDR) Then
Me.lblChkDup = "Has Duplicates"
Me.lblChkDup.ForeColor = lngRed
Me.TbSubForms.Pages.Item(2).Visible = True
Forms![frmMemberEntry]![frmDuplicates].Form![MembDups].SetFocus
Me.cmdMaster.Visible = False

ElseIf DLookup([ID], "tblDuplicates", "[MembDups] =" & Forms![frmMemberEntry]!VENDR) Then
Me.lblChkDup = "IS A DUPILCATE"
Me.lblChkDup.ForeColor = lngRed
Me.chkDup.Value = True
Me.TbSubForms.Pages.Item(2).Visible = True
Me.cmdMaster.Visible = True
Me.VENDR.ForeColor = lngGray
Me.VENDORNAME.ForeColor = lngGray
Me.cmbState.ForeColor = lngGray

Else

Me.lblChkDup = "Has Duplicates"
Me.TbSubForms.Pages.Item(2).Visible = True
End If

Exit_Form_Current:
    Exit Sub

Err_Form_Current:
    MsgBox Err.Description
    Resume Exit_Form_Current

End Sub

Thanks for any help that any can give!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I thought a printscreen of my entry form might help to show the visual of what I am trying to accomplished. I dirtied one up and here it is:

Thanks,

misscrf

It is never too late to become what you could have been ~ George Eliot