Different spin on find duplicates query

[sarahrosenberg]

I work for an insurance company and have a database of claims filed in January through March (MTD) by claim date. I am trying to count duplicate claims. I have been using the address field to count duplicates - two claims from the same address constitutes duplicate claims. Counting duplicates that have the same claim date is easy. I put in a totals / group by line, and told Access to count the claim numbers where the group by address was equal and set the criteria >1. But, this method does not count duplicate that have different claim dates - it only counts duplicates where all of the data in the record is the same. I also ran a query deleting the claim date and was able to get a count of all the duplicates by claim address. However, I now need to go back and re-link the claim date to the data. (I have over 400K records, so I cannot export the data to Excel, unfortunately. )

Any ideas???

 

[Andrew Fergus]

Hi
In your query design, change the 'Group By' under the date field to 'Where' and then you can put in your date criteria.
HTH, Andrew.

 

[sarahrosenberg]

I want the date field to show, I just don't want it included in the group field. Currently, the query is only considering two records "duplicates" if both the address and date match. I would like the query to evaluate duplicates based only on address - including only records that duplicate another record's address field.

Consider the following two records:
ADDRESS..............................DATE
24 Smith Street.....................1/23/05
24 Smith Street.....................1/26/05

Currently, my query will ignore these records and not consider them duplicates since the date is not the same. If I choose to not show the date field, the query sees them as duplicates, but I have lost my date data.

I am sorry if I am confusing...I am having trouble wording my question.

 

[Andrew Fergus]

Hi
If you want to show both dates which are potential 'duplicate claims', then you will need to do this with two queries. If you try to do this with one query it won't work.

The first query identifies the duplicate claims per my first post (with the date missing), so your current query (adapted per my post) will probably be ok. The second query is based on the first query as well as the claims table, linked by whatever unique field you have (claim number?). In this second query change the join properties to include all records from the first query and only matching records from the table. Then include all of the data from the first query and just the 'date' from the table.

This should return the results you described.

HTH, Andrew.