Trying to isolate duplicates via a query...

[Jim R]

There is no great way for me eliminate duplicates upon entry.  I can't just use first name and last name, because sometimes we have kids with the same name.  I can't really use their school because sometimes the parents just use the school name, other times they put HS after it or High School, etc.

 

So from year to year I get a lot of duplicate entries into my database.  The problem arises for me when a kid is registered but doesn't pay right away, so mom or dad return to the site to pay, and instead of just paying they re-register.  I'm trying to eliminate those.

 

Of course when that is the case, they generate a new ID so I started there:

select * from fallLeague10 as f1
join fallLeague10 as f2

where f1.nameFirst = f2.nameFirst
and f1.nameLast = f2.nameLast
and f1.confirm='1'
and f1.id <> f2.id


order by f1.nameLast, f1.nameFirst

(confirm = they entered this year)

 

 

I really can't make sense of why it's producing the list that it is.  I'm not really getting duplicates, maybe a couple, but there are definitely non-duplicates in that list.  Basically, what I'm looking to get is 8-12 names that show up twice (or three times in one instance).  It's a 470 person lot, and I just want to make sure I don't miss any because my numbers have to be exact or some kid ends up on two teams.

 

 

I figure a query would be better than scrolling through my list.

 

 

[sKunKbad]

You could eliminate the free typing of school names by providing a dropdown. If there are too many schools for a dropdown, then perhaps dynamically populate the school dropdown by the value of another dropdown, such as city.

 

Most students will have a home phone number, and while possible, it would be extremely rare that a family would have two kids with the same first name. Formatting of the telephone number ensures that it can be matched against the database, so a phone number is something you could use to identify a student.

 

SSN numbers are obviously very unique, and searching for them in the database is not difficult, even when you have encrypted the numbers.

 

I know people can change their phone number, but phone number combined with first name is a good indicator that you are dealing with the same student. Phone number alone can usually identify a home location.

[Jim R]

Problem is I don't ask for mobile numbers for every kid.  Younger kids don't have it.  Most families now don't really have or use home numbers, so I don't ask for it.  Cities and schools would be too plentiful, especially since I get kids from three states.

[sKunKbad]

Email address? I'm working on a project right now that allows a person to create their own account, but they must start by entering an email address, which then gets checked for an existing record, since email address is a unique field in the user table. If it's a parent's email address, they may have multiple kids, but doubtful that the parent will have more than one kid with the same name. I think it's safe to assume that every adult on the internet has an email address, so perhaps that can be your key. Students who sign up should be asked for their parent's email address, which would be a required field. It's just an idea...

[Jim R]

That would catch most of them from one season to the next and likely eliminate anyone re-registering simply to make a payment if they didn't pay the first time.  I'll try that for next year.  Thank you.

 

For now, anyone have any help for my query for my issue now?