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: 2
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

UtterAccess Editor
Posts: 18,223
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,


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
post Mar 1 2018, 10:47 AM

UtterAccess VIP
Posts: 9,567
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)





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

UtterAccess VIP
Posts: 9,210
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.

Robert Crouser

My company's website
Go to the top of the page
post Mar 1 2018, 11:27 AM

Posts: 925
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: 2
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    24th March 2018 - 12:50 PM