show records that are not completely duplicated 1

[iammcse]

Let say I have 4 fields,

field1 field2 field3 field4
1 2 3 4
1 2 3 4
1 2 3 4
2 6 3 5
1 1 2 1
1 1 2 1
5 9 7 4
7 7 9 7
7 7 9 7
7 7 9 5
7 8 9 10
8 8 6 8
8 8 6 9

I want to show all records that field3 has duplicates but if all those records of all fields are the same, then do not show any of them.

My result should be look like the folowing:

field1 field2 field3 field4
1 2 3 4
1 2 3 4
1 2 3 4
2 6 3 5
7 7 9 7
7 7 9 7
7 7 9 5
7 8 9 10
8 8 6 8
8 8 6 9

I am appling this to ACCESS. Please help. Thanks

 

[r937]

glad you said Access, i would've used COUNT DISTINCT but Access doesn't support that

select field1
     , field2
     , field3
     , field4
  from daTable
 where field3 in
       ( select field3
           from daTable
         group
             by field3
         having min(field1) <> max(field1)
             or min(field2) <> max(field2)
             or min(field4) <> max(field4) )

r937.com | rudy.ca

 

[iammcse]

ppl here are too smart and quick, I will try this right away. THX

 

[iammcse]

tried this and works like a champ. 1000 thanks!

 

[iammcse]

r937 I actually have this problem extended. Now you found all the records that field3 has the same value match. What if I want to find field3 has portion match, for example

field3
------
A100
A1004
A1004-56
A10044a-11

and they are belong to one group just like the former solution. Let say at least 4 first charater must be matched but no more than 6 charater extra charater at the end.

A100 <> A10044a-111 (not consider a match)

can it be done in a single query?

 

[r937]

um...

r937.com | rudy.ca