Finding duplicates without the "Find duplicates" wizard

[limperger]

Hello everyone!

Is out there any way to search for duplicate entries without using the "Find duplicates" option? In the Access 97 installed in my workplace, the Find duplicates option is disabled (don't ask me why) and I think that there are little chances of having it installed. Any wonder of how to overcome this situation without the aforementioned wizard?

Thank you very much in advance

Best regards from Barcelona

[MindBender77]

Hello everyone!

Is out there any way to search for duplicate entries without using the "Find duplicates" option? In the Access 97 installed in my workplace, the Find duplicates option is disabled (don't ask me why) and I think that there are little chances of having it installed. Any wonder of how to overcome this situation without the aforementioned wizard?

Thank you very much in advance

Best regards from Barcelona

The find duplicates query is set up in this format: You should be able to modify it for your needs.

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field1) In (SELECT [Field1] FROM [Table1] As Tmp GROUP BY [Field1] HAVING Count(*)>1 )))
ORDER BY Table1.Field1;
 

Hope this Helps,
Bender

[limperger]

The find duplicates query is set up in this format: You should be able to modify it for your needs.

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field1) In (SELECT [Field1] FROM [Table1] As Tmp GROUP BY [Field1] HAVING Count(*)>1 )))
ORDER BY Table1.Field1;
 

Hope this Helps,
Bender

Bender, first and foremost, thank you very much for your help!
I don't have much idea of SQL language, but this code looks like the one behind a Totals query. Am I right? Anyway, does this code enable Access to discriminate between, say, two Customer Names that, being in fact the same, differ from, say, a single or a couple of characters/numbers? I suspect that with this SQL code, this case would not be detected.
My question is, does the Find duplicates query/wizard discriminate between two records that are very similar, that is, does Access gives you a tip like: "hey, this two records have a 95% or more of similarity. Could they be a duplicate entry?"??. I don't know if this is possible, since, as I stated before, the Find duplicates option is disabled at my 97 Access.

Thank you in advance!!

Best regards

[PianoMan64]

Bender, first and foremost, thank you very much for your help!
I don't have much idea of SQL language, but this code looks like the one behind a Totals query. Am I right? Anyway, does this code enable Access to discriminate between, say, two Customer Names that, being in fact the same, differ from, say, a single or a couple of characters/numbers? I suspect that with this SQL code, this case would not be detected.
My question is, does the Find duplicates query/wizard discriminate between two records that are very similar, that is, does Access gives you a tip like: "hey, this two records have a 95% or more of similarity. Could they be a duplicate entry?"??. I don't know if this is possible, since, as I stated before, the Find duplicates option is disabled at my 97 Access.

Thank you in advance!!

Best regards

To answer that question, that answer is No, not out of the box. Not to say that you couldn't develop something that would say that. You could simply do a character by character compare and see how many of them match, and if the total is above a certain amount, you can simply display that record.

If you would like code examples, let me know. It would take a day to develop, but would be will to assist you in that.

Joe P.

[MindBender77]

Bender, first and foremost, thank you very much for your help!
I don't have much idea of SQL language, but this code looks like the one behind a Totals query. Am I right? Anyway, does this code enable Access to discriminate between, say, two Customer Names that, being in fact the same, differ from, say, a single or a couple of characters/numbers? I suspect that with this SQL code, this case would not be detected.
My question is, does the Find duplicates query/wizard discriminate between two records that are very similar, that is, does Access gives you a tip like: "hey, this two records have a 95% or more of similarity. Could they be a duplicate entry?"??. I don't know if this is possible, since, as I stated before, the Find duplicates option is disabled at my 97 Access.

Thank you in advance!!

Best regards

I agree with pianoman. You would have to develop a query to work in the manner that you specified. As for the SQL I provided, this was the SQL produced from a "Find Duplicate" query in a testing database I developed using the wizard. It simply states:

Show field1 and field2 where exact duplicates exist in field1. This example looks at the entire record for matches. Perhaps you could build on to it.

Bender

[limperger]

To answer that question, that answer is No, not out of the box. Not to say that you couldn't develop something that would say that. You could simply do a character by character compare and see how many of them match, and if the total is above a certain amount, you can simply display that record.

If you would like code examples, let me know. It would take a day to develop, but would be will to assist you in that.

Joe P.

Joe:

Thank you very much for your offering. Anyway, this is not a top priority for me right now and I would not want you to spend an entire day on this.
Indeed, thank you very much! I would like to offer some of my knowledge in return but I am afraid my Access knowledge does not go beyond the fundamentals that can be read in the common textbook (like Access The Bible, The Reference and stuff like that). In fact, I am stunned by the knowledge people in here possess!!!

Best regards from Barcelona