Find Duplicate Query

  • Thread starter Thread starter Mary
  • Start date Start date
M

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?
 
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]));
 
Does it work with a small number of records? It did on my machine. How
many records are in your table?

Mary said:
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]));

Mary said:
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?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top