How do I look at a previous record in access for duplicates?

[Guest]

I have a situation where I have an identification number for each site. But
sites that are grouped together with one supervisor share the first 6 numbers
in the identification. I would like to go through my list and weed out the
duplicates that are under one supervisor and only take the record that ends
in "000". The sites that are not sharing a supervisor do not necessarily end
in "000", so I must look at the first 6 numbers to see if they are the same
as the previous record. How do I do that?

 

[John Spencer]

What is the full structure of the site number? Is the structure consistent in
length? The following may give you a start on what you want.

SELECT MIN(SiteID) as UniqueSiteNumber
FROM SiteIDTable
GROUP BY Left(SiteID,6)

You could use that as a subquery tgo give you more infor.

SELECT SiteIDTable.*
FROM SiteIDTable
WHERE SiteIDTable.SiteID IN
(SELECT MIN(SiteID) as UniqueSiteNumber
FROM SiteIDTable
GROUP BY Left(SiteID,6))