Query for duplicate field value

[kcary]

I'm using the standard 'In(Select...' to find records with duplicate field values. Now that I have the list, I'd like to view only those where all fields in the duplicate records are not equal.
For instance:

a b c d - will show
a b c x

a b c d - will not show
a b c d

I've tried everything I can think of. Unfortunately, I'm not very SQL-friendly, and the syntax of the statements to make use of Count is apparently beyond me.

 

[lespaul]

in your first SELECT statement before the first field name, add DISTINCT.

SELECT DISTINCT FIELD1, FIELD2 ....

HTH

Leslie

 

[kcary]

Running the SELECT DISTINCT query against a copy of the live database took >15 minutes to reach 100% and wasn't responsive after reaching 100%.
I don't see a change. I'm using a table very much like the example I cited above:

Field1 Field2 Field3 Field4
a b c d
a b c d
x b c d
x b c x
x b c x

Current query:
In (SELECT [Field1] FROM [tblDummy] As Tmp GROUP BY [Field1] HAVING Count(*)>1 )

 

[lespaul]

what about the rest of your query. It can't just be

In(select....)

and it may indeed take >15 depending on how many records are in the table.

Leslie

 

[Golom]

Depending on how many fields are involved, this may be doable (is that a word?) with SQL or you may need to resort to VBA. The problem with SQL is that you need to do field-by-field comparisons and, if there are a lot of fields, that gets a bit onorous. For example
[blue][tt]
Select *

From tbl A INNER JOIN tbl B
ON A.f1 = B.f1
AND A.f2 = B.f2
AND A.f3 = B.f3

Where A.f4 <> B.f4
OR A.f5 <> B.f5
OR A.f6 <> B.f6
OR A.f7 <> B.f7
etc.
[/tt][/blue]
This is the equivalent of your "IN" written as a join on an arbitrarily selected three fields. Add or remove fields from the "ON" condition as needed to specify the combination of fields that you want to test for duplicates.

The real challenge is that, if your table has many fields then each of the fields will need to appear in the "Where" clause.

If this works for you ... Great!

If not, post back and we can examine the VBA alternatives.

 

[kcary]

I'm going to try to answer two posts at once here.

lespaul - The live table is 4057 records w/ 42 fields (a few are text/memo for good measure). This scanning of duplicates happens 2-3 times per day, so I don't know if the wait can be justified. I'll let it sit overnight to ensure that it does respond at some point.

Golom - I'll try this tomorrow AM. Of the 42 fields, only 15-20 are possible mismatches. I much prefer VB, though, so if you have time to post a suggestion before tomorrow, I'll try that angle, too.

 

[kcary]

Can the first section also contain:

From qry A INNER JOIN tbl B

where the query is actually the list of records with duplicate values?