X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Finding Duplicates Within A Range, Any Version    
post Mar 1 2018, 10:30 AM

Posts: 5
Joined: 12-December 17

I'm going a little crazy over finding duplicates with a range.
I know how to create a query to identify exact duplicates.

See data below
strSQL="SELECT TestData.Number, Count(TestData.ID) AS CountID " & _
"FROM TestData " & _
"GROUP BY TestData.Number " & _
"HAVING Count(TestData.ID)>1;"

However, How can I use a range such that records 3,4,5 are treated as duplicates?
Many thanks!

ID Number
1 1
2 2
3 2.9
4 3
5 3.1
6 4
7 5
Go to the top of the page
post Mar 1 2018, 10:45 AM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


If you use a Totals query, there's no way to identify each duplicate row; just the duplicate value. You could count how many rounded values are equal to this rounded value in a Select query...

SELECT ID, Number, IIf(DCount("*","TestData","Round(" & [Number] & ")=Round([Number])")>1,"Duplicate","") AS Dupe
FROM TestData;

BTW, your field names should really be fixed. "ID" is too vague to be useful (if this is a table of tests, then "TestID" would be more descriptive), and "Number" is a reserved word, I think.

Hope this helps,

Go to the top of the page
post Mar 1 2018, 10:47 AM

UtterAccess VIP
Posts: 10,609
Joined: 6-December 03
From: Telegraph Hill


You can try:
strSQL = "SELECT Round(TestData.Number), Count(TestData.ID) AS CountID " & _
         "FROM TestData " & _
         "GROUP BY Round(TestData.Number) " & _
         "HAVING Count(TestData.ID)>1;"

(btw 'Number' is not a good choice for a field name)


Go to the top of the page
post Mar 1 2018, 10:57 AM

UtterAccess VIP
Posts: 10,350
Joined: 10-February 04
From: South Charleston, WV

If 3,4, and 5 are the only duplicates create a column with the Iif function, otherwise create a column with the Switch function.
Go to the top of the page
post Mar 1 2018, 11:27 AM

Posts: 992
Joined: 26-March 08
From: London, UK

Here's another possibility (see attached).

I've used a custom rounding function, as the Access built-in Round() function rounds to even (i.e. 3.5 rounds to 4 but 2.5 rounds to 2, not 3).

BTW: If you can you have negative numbers in your data, you may need to re-work a little to make sure you get the correct results.


Attached File(s)
Attached File  DupsWithnRange.zip ( 21.92K )Number of downloads: 4
Go to the top of the page
post Mar 12 2018, 01:00 PM

Posts: 5
Joined: 12-December 17

I apologize for not responding sooner to all the solutions offered for my issue.
It was stupid of me not to figure out a solution using the rounding function.
Because I like to work in steps, I created a new column to store the rounded data.
Then simply used the Count > 1 query to find the relevant duplicates.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    11th December 2018 - 02:51 AM