Duplicate record detector...

[Guest]

Hi

I'm working on a database application which needs to have a "dupplicate"
detection tool for records. OK Point is that: if three fields are actually
repeated (on different Records) a message is suppoust to print on the screen
in order to get the user warn there's a posible duplication of a record or of
a "procedure". -->It's for an insurance company<-- so in order to detect a
"dupplicate" of a "procedure"; I need to do a "procedure" to detect that.
Point is that I can not use the "Primary-Key" because there might be a
duplication of a field when if a "patient" asked for a second opinion to
another phisician. Or another medical procedure from another phisician.

I hope this story does makes scence.

Reboot Kid

 

[Smartin]

Hi

I'm working on a database application which needs to have a "dupplicate"
detection tool for records. OK Point is that: if three fields are actually
repeated (on different Records) a message is suppoust to print on the screen
in order to get the user warn there's a posible duplication of a record or of
a "procedure". -->It's for an insurance company<-- so in order to detect a
"dupplicate" of a "procedure"; I need to do a "procedure" to detect that.
Point is that I can not use the "Primary-Key" because there might be a
duplication of a field when if a "patient" asked for a second opinion to
another phisician. Or another medical procedure from another phisician.

I hope this story does makes scence.

Reboot Kid

First thought is, perhaps a multiple field index on whatever table is
storing this information would prevent duplicate records from being
created in the first place.

If it's too late for that, next thought is, if you want to check fur
duplicates one record at a time (as if from a form) write a select query
that concatenates all three fields in question into one column and see
if you get more than row returned.

I suspect the success of either of these methods will depend heavily on
the nature of the data you want to be unique. Dates and other numbers
compare well, perhaps procedure codes and claim numbers do also,
depending on how well you control the input on these fields. Names
(patient, provider, procedure description, etc.) are fallible as extra
spaces, punctuation, abbreviations et. al. can throw off a match.

There are probably better ways, but I hope this gives you some ideas. If
you are still stuck would you give us some examples of what your table
structure and data looks like?