How Can I Create a Query to Distinguish Between Two Similiar Records?

[VAST39]

Hey guys,

I am currently trying to solve a problem in my database dealing with duplicate records. Here's the situation. I have a database for employee tax information, and they wanted to add a field for a checkbox. So I created the field with a YES/NO value. Then I ran an update query to set the values to YES At that point, I discovered that some employees have two different tax amount records. My query set the value to YES for both records. Well of course, they only want a checkbox on one of the records. I have to find a way to run another update query that will set the value to NO for the second record.


Here's an example dummy record


SSN NAME AMOUNT TOTAL DIST

12345894 JENKINS, DAVID $23.55 YES
12345894 DAVID L JENKINS $241.22 YES


I want a query to set the record with $241.22 to NO. The only thing I can find different between the two records is that the 2nd record has a different name format. Is there any way to write a query that can look for names with a comma separation?


Thanks for any help here.

 

[SkipVought]

Hi,

Well it's a missing comma in THIS PARTICULAR CASE.

Are there other cases where there are multiple rows for the same SSN that all have commas in the Name?

 

[SQLBill]

There are two differences. Name order and the dollar amount. How do you determine which dollar amount is correct? Using this information; you say the one with no commas is 'wrong'. Are you sure? That would make the $241.22 value "wrong" also.

SSN NAME AMOUNT TOTAL DIST

12345894 JENKINS, DAVID $23.55 YES
12345894 DAVID L JENKINS $241.22 YES

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

If you know for a fact that you want to change the ones with no comma, using CHARINDEX should work. Just check where CHARINDEX(',', [name], 1) = 0. That should return any records where there isn't a comma in the Name column.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[VAST39]

Thanks guys! Yes, all the records that I want to change do not have a comma in the name. I'll give the CHARINDEX a shot.