Find Duplicate Query

[Mary]

I am trying to run a query where the value of a particular
field [LtrType] is repeated. I only need to see results
if the repeated field is the next record. If there is a
record in between the repeated fields, I don't need to see
it. I tried using the find duplicates wizard, but can't
seem to get the "next record" piece right. Can anyone
help?

 

[John Verhagen]

Assuming the table is called tblRepeat, and you have a field with unique
numbered values called ID, try the following to get the record that is
repeated the second and subsequent times:

SELECT R.ID, tblRepeat.LtrType
FROM tblRepeat, tblRepeat AS R
WHERE (((R.ID)=DMin("[ID]","tblRepeat","[tblRepeat].[ID]>" &
[tblRepeat].[ID])) AND ((tblRepeat.LtrType)=[R].[LtrType]));

 

[John Verhagen]

Does it work with a small number of records? It did on my machine. How
many records are in your table?

I tried this and it just clocks. Is there anything else
I can try? The Access97 database I work with is on a
network server, so that may be part of the clocking
problem, but if there's an alternate method I'd like to
try.

-----Original Message-----
Assuming the table is called tblRepeat, and you have a field with unique
numbered values called ID, try the following to get the record that is
repeated the second and subsequent times:

SELECT R.ID, tblRepeat.LtrType
FROM tblRepeat, tblRepeat AS R
WHERE (((R.ID)=DMin("[ID]","tblRepeat","[tblRepeat].[ID]
" &
[tblRepeat].[ID])) AND ((tblRepeat.LtrType)=[R]. [LtrType]));

I am trying to run a query where the value of a particular
field [LtrType] is repeated. I only need to see results
if the repeated field is the next record. If there is a
record in between the repeated fields, I don't need to see
it. I tried using the find duplicates wizard, but can't
seem to get the "next record" piece right. Can anyone
help?

.