Access 03 - dupe numbers

[ddw23]

Hi All.

I have a db table of vendors for City and County. The vendor number is 8 characters for City (78945612) and 9 for County (the 9th is a "P" - 78945612P). I need a query that will find all dupe vendor numbers (first 8 characters) regardless of the "P".

Will someone please help?? Thanks!!!

 

[Peter Reid]

Something like;

SELECT First(Left([VendorNo],8)) AS [CityCode], Count(VendorNo) AS NumberOfDuplicates
FROM tbl_Vendors
GROUP BY Left([VendorNo],8)
HAVING (((Count(VendorNo))>1));