Duplicate Query Problem

[sross]

Help! I am trying to manipulate a find duplicates query using the following criteria: Fstnm L2, Lstnm L5, Add1, Zip - can't figure out what I am not doing.
This is what I have done so far:
SELECT [Duplicate Identification Dataset].[FSTNM], [Duplicate Identification Dataset].[LSTNM], [Duplicate Identification Dataset].[ADD1], [Duplicate Identification Dataset].[ZIP], [Duplicate Identification Dataset].[ID], [Duplicate Identification Dataset].[MIDNM], [Duplicate Identification Dataset].[SPFSTNM], [Duplicate Identification Dataset].[SPMIDNM], [Duplicate Identification Dataset].[SPLSTNM], [Duplicate Identification Dataset].[ADDRIMPID], [Duplicate Identification Dataset].[ADD2], [Duplicate Identification Dataset].[CITY], [Duplicate Identification Dataset].[TERRITORY], [Duplicate Identification Dataset].[COUNTRY], [Duplicate Identification Dataset].[CORCODE], [Duplicate Identification Dataset].[SEGMENT], [Duplicate Identification Dataset].[PRIORITY], [Duplicate Identification Dataset].[MAILED_IN_]
FROM [Duplicate Identification Dataset]
WHERE ((([Duplicate Identification Dataset].[FSTNM] In (SELECT [FSTNM] FROM [Duplicate Identification Dataset] As Tmp GROUP BY left([FSTNM],2),left([LSTNM],5),[ADD1],[ZIP] HAVING Count(*)>1 And left([LSTNM],5) = left([Duplicate Identification Dataset].[LSTNM],5) And [ADD1] = [Duplicate Identification Dataset].[ADD1] And [ZIP] = [Duplicate Identification Dataset].[ZIP]))))
ORDER BY [Duplicate Identification Dataset].[FSTNM], [Duplicate Identification Dataset].[LSTNM], [Duplicate Identification Dataset].[ADD1], [Duplicate Identification Dataset].[ZIP];

 

[Isskint]

hi sross

We all love to help, but no one would have a clue where to start.

How is it not working?
Do you get an error (which error)?
Does it return wrong data? Please supply some data and example of what you expect and what you get.
Do you get no data returned? Please supply some data and example of what you expect and what you get.

There are a couple of possible logic issues in your code, eg And [ADD1] = [Duplicate Identification Dataset].[ADD1] And [ZIP] = [Duplicate Identification Dataset].[ZIP])))). I say possible beacause this may be correct but generally it is advisable to isolate multiple criteria in brackets, so each bracketed criteria is evaluated separately. It may be this is correct, but again without knowing the exact issue, i could not say.