UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Duplicates, Access 2010    
 
   
rzw0wr
post Nov 13 2019, 11:52 PM
Post#1



Posts: 321
Joined: 28-March 12
From: Indiana


I have a list of names in a table.
ID, FirstName and LastName.

I created a duplicate query to find them.
Is there a way to send the user a message from that query or is there another better way to find duplicates in a table.


Thank you,
Dale

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 
theDBguy
post Nov 14 2019, 12:09 AM
Post#2


UA Moderator
Posts: 76,845
Joined: 19-June 07
From: SunnySandyEggo


Hi Dale. Those are two separate process. You got one done, just need to do the other. Do you have the email info as well?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rzw0wr
post Nov 14 2019, 12:30 AM
Post#3



Posts: 321
Joined: 28-March 12
From: Indiana


You have me a little confused.

What email info are you looking for wink.gif

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 
nvogel
post Nov 14 2019, 08:09 AM
Post#4



Posts: 1,039
Joined: 26-January 14
From: London, UK


I think theDBguy's point may be that it isn't clear what emails you want to send. Are you trying to send emails to every person returned in your query or are you trying to send one email to someone that includes the *results* of your query? If it's the former then you must have the email addresses listed somewhere that you haven't specified. If it's the latter then presumably someone still has to push a button to execute the query, in which case sending the email can be part of the process executed when the button is pushed. In either case, sending the email is different to running the query.
Go to the top of the page
 
Jeff B.
post Nov 14 2019, 09:23 AM
Post#5


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


If that second column contains the concatenated First Name and Last Name, it might present some difficulties down the line. For example, how would you sort by "Last Name"?

Also, if you have a "Rob Smith", and a "Robert Smith", and a "Bob Smith", how will you/your users know if those are all the same person. For that matter, what if you legitimately have more than one "Rob Smith"?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
rzw0wr
post Nov 14 2019, 05:17 PM
Post#6



Posts: 321
Joined: 28-March 12
From: Indiana


I am not sending email to anyone.

The list is a list of customers.

I want to make sure that a customer is not entered more than once on the list.

The query I have now finds these duplicates for me.

If there are 2 or more John Does the they would need something to ID them as different people.

I am trying to figure out what I can do with the query.
If I make it a delete query then it deletes both names on the list.

Thanks for the replies
Dale

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 
Gerrit
post Nov 14 2019, 05:28 PM
Post#7



Posts: 12
Joined: 12-October 19



As it is possible to have two or more Robert Smiths (being different persons with the same name) in your database then the alternative is to search for duplicates in other fields for each person. Say search for identical address's for each Robert Smith?
Go to the top of the page
 
orange999
post Nov 14 2019, 07:02 PM
Post#8



Posts: 1,995
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


To differentiate customers whose names are duplicates, you will need other criteria to make a determination of uniqueness or not.
Perhaps phone number, street address/state/city/zipcode, BirthDate.....

--------------------
Good luck with your project!
Go to the top of the page
 
WildBird
post Nov 14 2019, 07:22 PM
Post#9


UtterAccess VIP
Posts: 3,673
Joined: 19-August 03
From: Auckland, Little Australia


And just to add to the mix, you might 2 Robert Smith's with different addresses, but what is to say this isn't the same person, and they simply moved? Not an easy one if you don't have a truly unique field for each person.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
rzw0wr
post Nov 14 2019, 10:56 PM
Post#10



Posts: 321
Joined: 28-March 12
From: Indiana


I got it.
Problem solved.

I used a little DAO in VB.

If the customer moved then my niece would change their address.

Thank you for the replies,
Dale
This post has been edited by rzw0wr: Nov 14 2019, 10:58 PM

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 
FrankRuperto
post Nov 30 2019, 08:14 PM
Post#11



Posts: 345
Joined: 21-September 14
From: Tampa Bay, Florida, USA


If you are capturing their date of birth, it helps to locate duplicates because it is very rare that you find two or more people with the same last names and same date of births. I use the following qry to identify duplicate customer records.

CODE
SELECT [LastName], [FirstName], [DateOfBirth], [IdentType], [IdentNum], [Address1], [Address2], [City], [State], [TelephoneNum1]
FROM tblCustomer
WHERE (((tblCustomer.LastName) In (SELECT [LastName] FROM [tblCustomer] As Tmp GROUP BY [LastName] HAVING COUNT(*) > 1 )))
ORDER BY tblCustomer.LastName, tblCustomer.FirstName;




--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
nvogel
post Nov 30 2019, 11:19 PM
Post#12



Posts: 1,039
Joined: 26-January 14
From: London, UK


Hi Frank,

The problem with using IN like that is that it limits you to comparing one column at a time. Using a join instead means you can include as many columns as you like for the duplicate checking criteria.

SELECT c.*
FROM tblCustomer AS c,
(SELECT LastName, FirstName FROM tblCustomer GROUP BY LastName, FirstName HAVING COUNT(*)>1) AS d
WHERE c.LastName = d.LastName AND c.FirstName = d.FirstName;

Obviously names tend to be very frequently duplicated and don't necessarily help you find actual duplication problems. As regards data of birth, probably most companies can't or won't ask customers for that information. In some situations (e.g. employment or health related) keeping the customer's date of birth is OK but those seem like the exception rather than the rule. The data protection practice of not capturing more data than required is called minimisation and in some jurisdictions it is the law and not just a good practice.
Go to the top of the page
 
FrankRuperto
post Dec 1 2019, 02:25 AM
Post#13



Posts: 345
Joined: 21-September 14
From: Tampa Bay, Florida, USA


My users are pawnbrokers so DoB's and all other info on customers photo ID's are captured when users feed their ID's into a scanner. One column, like LastName, is fine because the dups report is filtering custs with the same last names and same DoB's, and the report also shows all the other info that's on the scanned ID's. So we have a larger result set that we can visually examine to see if there are too many coincidences. Sometimes existing customers supply different ID's and if the users dont visually recognize the customer, and those customers say they've never done business there before, then duplicate records slip through the cracks. On a similar topic, some users wanted the ability to add new values on_the_fly in certain comboxes like ItemNames, Brands, Models and they ended up adding mispellings and synonyms of existing values, despite all the training, cheat sheets I provided, plus I emphasized to first check the existing values before deciding to add new one's, ugh pullhair.gif
This post has been edited by FrankRuperto: Dec 1 2019, 02:39 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
Jeff B.
post Dec 1 2019, 08:51 AM
Post#14


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


Frank

I wish rare duplicate names + birthdates were true … I once worked with a fellow who spent the better part of a year convincing "the authorities" that he wasn't the person they were looking for. Turned out he shared a name AND birthdate with a serious felon living 100 miles away. … and one of the other fellows posting here relates that there were TWO other folks besides him with the same name working at the same organization. Go figure!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
FrankRuperto
post Dec 1 2019, 10:04 AM
Post#15



Posts: 345
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Jeff B.

Those exceptions will occur and authorities rely on fingerprints and dna to resolve a person's identity, but for a business that tracks say 10,000 people, the odds are still very low. So far I have not detected two or more different people with the same last names and DoB's within one pawnshop site. Spanish last names where my users are located consist of father's surname and mother's surname, so that lowers the probabilities even more, but that still does not guarantee it will never happen. My app has a customer history table that tracks any changes made to a customer's profile data. When a new or existing customer provides a photo ID, the app does a lookup in the history table to see if that ID's info already exists and displays the customer record if true, so that also helps prevent dup customers. We are planning to implement fingerprint scanners since regulations require their capture anyway when customers pawn or sell stuff, and we can leverage those fingerprints to uniquely identitfy customers. But as nvogel mentioned earlier, not all business models capture DoB's and other personal data, and with the rise of identity theft I can see these organizations that track large amounts of people grappling with duplicate records, or records with slight differences that belong to the same person.
This post has been edited by FrankRuperto: Dec 1 2019, 10:58 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
FrankRuperto
post Dec 1 2019, 12:04 PM
Post#16



Posts: 345
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Despite all the safeguards we can implement to minimize the creation of more than one master record for a same person, we can not totally prevent them and automate their detection, and must rely on humans to periodically examine suspect data. So what happens when we do find more than one master record which belongs to the same person, and the master table has some or many related detail tables with data in them?... Which master record do we keep, and which detail records that belonged to the duplicate masters do we merge with the master record we kept?... We attempted to automate the process of merging the detail data to the master that was selected to be preserved by updating all the foreign keys, but this proved to not be practical because this process involves human decision-making as to which detail records to merge and which one's to discard. Our users are also not capable of doing this on their own, so we collaborate with them in deciding what to keep, what to discard and do it for them. This and maintaining some of their lookup tables is part of our support services.
This post has been edited by FrankRuperto: Dec 1 2019, 12:43 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
gemmathehusky
post Dec 2 2019, 07:42 AM
Post#17


UtterAccess VIP
Posts: 4,750
Joined: 5-June 07
From: UK


The problem is that computer data is not the same as the underlying real world data.

That's why although you can design a perfect system using surrogate autonumber keys, you need some unique real world information to manage and prevent "duplicates" in your table. For very big data populations, eg the population of a country, it's next to impossible to manage without error.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
WildBird
post Dec 2 2019, 02:49 PM
Post#18


UtterAccess VIP
Posts: 3,673
Joined: 19-August 03
From: Auckland, Little Australia


Frank,

A friend of mine was working at a bank, and was typing names into a system. She came across 2 that had very similar names, literally 1 letter different, something like Antonio and Antonion (cant remember real names, but similar and very 'rare' to say the least one of the names). Same data of birth, same address etc. She deleted the one with the weird name thinking it was a typo. Turns out, they were twins. Major issue to put the deleted one back into the system. So twins, same data of birth, same last name, same address. Twins are not exactly rare I guess.

DNA and fingerprints. I will argue that these are not definite. They might be able to tell if they ARE not a match, but can not guarantee that they are a match. Close, but no way to guarantee that with current technology that there are not 2 people with 'identical' fingerprints or DNA, either alive now, or previously alive, or even being born in the future.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
nvogel
post Dec 2 2019, 03:34 PM
Post#19



Posts: 1,039
Joined: 26-January 14
From: London, UK


There's no need for anything like DNA in most cases. Having the same person signed-up multiple times for a business relationship isn't necessarily a problem.

I might open a second account with Amazon under a different login just so that I can discreetly buy a birthday present for my wife. That's not a problem for Amazon as long as I'm not trying to defraud them. In fact de-duplicating and closing my second account might even be considered unethical, contrary to data protection laws and/or a breach of terms and conditions. The business key for my accounts would be the login name which would be unique for each account (probably a unique email address would be required as well).

In banking there has to be a "know your customer" process that ensures accounts get assigned to a suitably identified person. Multiple identification methods can be used but what matters is due diligence, compliance with regulations and achieving a satisfactory level of fraud-prevention.
Go to the top of the page
 
FrankRuperto
post Dec 2 2019, 05:35 PM
Post#20



Posts: 345
Joined: 21-September 14
From: Tampa Bay, Florida, USA


WildBird,

Parents usually name twins differently to disambiguate, so your example is an extreme rarity. When detecting possible dups, users should investigate the data against realworld facts before merging data. A good design is to never physically delete records, rather logically delete using a flag field and selecting a reason code. Implementing a related history table that tracks any profile changes also provides an audit trail. Government agencies like Social Security and Dept. Motor Vehicles combine records so they can reference history of all data. There are people who legitimately have more than one socail security number, legally changed their names, etc. Fingerprints and DNA are not totally unique, but they are 99.98% reliable. Scanning photo ID's that have PDF417 barcodes helps prevents data entry errors, but the data on the photo ID could also be wrong. But atleast we are able to minimize the probabilities of duplicate records and incorrect data.

nvogel,

Some business cases warrant having only one customer profile record per person. Banking systems use a centralized CIF (Customer Information FIle) per customer. One customer can have several accounts, but the are all linked to the customer's unique CIF record. If bank discovers more than one CIF for the same customer, they will merge all the detail data to the CIF that will remain active and inactivate (logically delete) the other dup CIF records.
This post has been edited by FrankRuperto: Dec 2 2019, 05:39 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 02:23 AM