searching for filtered duplicates in access query

Question

i have the following problem in MSACCESS 2003

i have a query called A_Gravur which 3 columns SP1, SP2, SP3

now i want to search for duplicates.

query A_Gravur:

SP1 | SP2 | SP3

64 | CB75000 | 3

64 | empty | D

70 | CB70010 | 1

119 | CB70050 | 5

119 | empty | D

165 | S15002 | Schalter

194 | R70034 | Poti

194 | R10023 | Potentiometer

196 | S10063 | Schalter

197 | S10063 | Schalter

198 | S10070 | 3

199 | CB75000 | 5

first: filter duplicates in SP1 from A_Gravur

result:

SP1 | SP2 | SP3

64 | CB75000 | 3

64 | empty | D

70 | CB70010 | 1

119 | CB70050 | 5

119 | empty | D

165 | S15002 | Schalter

194 | R70034 | Poti

194 | R10023 | Potentiometer

196 | S10063 | Schalter

in the first duplicate search are too many results! it should be filtered like this: Filter (IF one result in SP3 is letter D then dont show it) Filter (IF [SP1 and SP2] = [SP1 and SP2 from next record] then dont show it

Second: filter duplicates in SP2 from A_Gravur

SP1 | SP2 | SP3

64 | CB75000 | 3

199 | CB75000 | 5

196 | S10063 | Schalter

197 | S10063 | Schalter

my final result should look like this it is the result from SP1 duplicate search with filter + SP2 duplicate search

SP1 | SP2 | SP3

194 | R70034 | Poti 470k

194 | R10023 | Potentiometer

196 | S10063 | Schalter

197 | S10063 | Schalter

64 | CB75000 | 3

199 | CB75000 | 5

any idea how to solve my problem? a function or a sql query?

I'm trying to solve your problem, but there are some problems with your test data. If I understand correctly, there should be two rows beginning 70 in the final output and the row beginning 199 should not appear. If two rows are identical, like those beginning 196, do you want two rows in the output or is one copy OK ? — grahamj42, Mar 10 '13 at 15:13
hi there, thank you very much for your help. 64 and 199 are identical in column sp2 and should appear because they are different in sp1. also the two lines 194 because they are different in sp2. it also should appear one of the two lines with 196 and 197 because they are different in sp1. i did a mistake in my final output sorry. i will correct it — user2153413, Mar 10 '13 at 19:18
i need two rows in the output because the final user must search for the mistake — user2153413, Mar 10 '13 at 19:19
You say 64 should appear, but the second row 64 has D in SP3...OK, I understand, you test for either of field SP1 or SP2 duplicate ... it get more complicated :-( — grahamj42, Mar 10 '13 at 19:21
hmm sorry i wrote this post with a tablet, if there is a D in SP3 then there is nothing in SP2. so if there is the duplicate check on SP2 there should only be 2 findings — user2153413, Mar 10 '13 at 19:30

grahamj42 · Accepted Answer · 2013-03-10T20:45:24.633

I think this SQL query will give you what you want :

SELECT SP1, SP2, SP3
  FROM A_Gravur
  WHERE (SP1 IN
    (SELECT SP1
      FROM A_Gravur
      WHERE SP3<>'D'
      GROUP BY SP1
      HAVING COUNT(*)>1)
  OR SP2 IN
    (SELECT SP2
      FROM A_Gravur
      WHERE SP3<>'D'
      GROUP BY SP2
      HAVING COUNT(*)>1))
  AND SP3<>'D'
  ORDER BY SP1, SP2;

` You can play with this at http://sqlfiddle.com/#!2/7351d/16

To eliminate the lines entirely duplicated, it becomes more complicated :

SELECT SP1, SP2, SP3
  FROM A_Gravur
  WHERE (SP1 IN
    (SELECT SP1
      FROM A_Gravur
      WHERE SP3<>'D'
      GROUP BY SP1
      HAVING COUNT(*)>1)
  OR SP2 IN
    (SELECT SP2
      FROM A_Gravur
      WHERE SP3<>'D'
      GROUP BY SP2
      HAVING COUNT(*)>1))
  AND CONCAT(CAST(SP1 AS CHAR),SP2,SP3) NOT IN
    (SELECT CONCAT(CAST(SP1 AS CHAR),SP2,SP3)
      FROM A_Gravur
      WHERE SP3 <> 'D'
      GROUP BY CONCAT(CAST(SP1 AS CHAR),SP2,SP3)
      HAVING COUNT(*)>1)
  AND SP3<>'D'
  ORDER BY SP1, SP2;

This can be found at http://sqlfiddle.com/#!2/7351d/35

Note that CONCAT(CAST(SP1 AS CHAR),SP2,SP3) is MySQL syntax for SQL Fiddle.

In MS Access, one would use Format(SP1) & SP2 & SP3

thank you very much this is a really good result. Is it possible to remove those lines which are simular? i mean the number the two numbers from 70, 165 which are the same in SP1 and SP2? — user2153413, Mar 10 '13 at 20:10

searching for filtered duplicates in access query

query A_Gravur:

SP1 | SP2 | SP3

result:

SP1 | SP2 | SP3

Second: filter duplicates in SP2 from A_Gravur

SP1 | SP2 | SP3

SP1 | SP2 | SP3

1 Answers1

Linked