Re: Finding and deleting dupe queries across two fields

JOhn,

That didn't work. (it just slowly goes through small progress bar botom left
of screen and eventually results nothing)

When you mention 'failure due to the table name being [Street Names] instead
of StreetNames (no spaces).' Can I just rename the query (it is independent
of everything else). I don't really understand "If it does, you will have to
build a series of queries to do this." I can't envision this series of
queries.





"John Spencer" wrote:

Do you have a primary key field in the table street names? And if so is
it a single field?

For instance if StreetNameID is unique for every record in the table,
you can use the query below to delete selected records.

DELETE *
FROM [Street Names]
WHERE StreetNameID in
(SELECT Max([Street Names].StreetNameID)
FROM [Street Names] as Tmp2
WHERE Tmp2.StreetName In
(SELECT StreetName
FROM [Street Names] As Tmp
GROUP BY StreetName, PostCode
HAVING Count(*)>1 AND Tmp.PostCode = Tmp2.PostCode)
GROUP BY Tmp2.StreetName)

That may fail due to the table name being [Street Names] instead of
StreetNames (no spaces). If it does, you will have to build a series of
queries to do this.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


efandango wrote:
Thanks John,

But how do I delete the dupes leaving a single record of each entry?



"John Spencer" wrote:

You can try the following modification. I think it may give you the result
you are looking for.

SELECT [Street Names].StreetName
, [Street Names].StreetNameID
, [Street Names].Postcode
FROM [Street Names]
WHERE [Street Names].StreetName In
(SELECT [StreetName]
FROM [Street Names] As Tmp
GROUP BY [StreetName], PostCode
HAVING Count(*)>1 AND Tmp.PostCode = [Street Names].PostCode)
ORDER BY [Street Names].StreetName;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"efandango" <efandango@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:87A5E01D-930C-4917-9A3A-3954AE3E6162@xxxxxxxxxxxxxxxx
How can I display duplicate addresses with matching postcodes. At the
moment
my SQL will only find duplicate Street Names, which will only be duplicate
if
they also have a matching postcode. Because like a lt of big cities, you
can
have more than one identical street name, but it is when combined with the
postcode that it becomes unique, but with possible duplicates.

Below is the SQL to find duplicate Street Names, but I need it to match
against the corresponding postcode to find the duplicate addresses.

How can i find the dupes across both fields; [StreetName] - [Postcode] and
then delete just the dupes, leaving the one copy of each record intact?




SELECT [Street Names].StreetName, [Street Names].StreetNameID, [Street
Names].Postcode
FROM [Street Names]
WHERE ((([Street Names].StreetName) In (SELECT [StreetName] FROM [Street
Names] As Tmp GROUP BY [StreetName] HAVING Count(*)>1 )))
ORDER BY [Street Names].StreetName;




.

Relevant Pages

  • Re: Finding and deleting dupe queries across two fields
    ... I do have a primary key field in the table street names. ... "John Spencer" wrote: ... GROUP BY, PostCode ... my SQL will only find duplicate Street Names, ...
    (microsoft.public.access.queries)
  • Re: Bulk Update To Db Tables
    ... above that have lots of duplicate entries. ... I guess the Update Query would do the trick? ... As to whether to use append or update queries, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Bulk Update To Db Tables
    ... above that have lots of duplicate entries. ... I guess the Update Query would do the trick? ... As to whether to use append or update queries, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Finding and deleting dupe queries across two fields
    ... Do you have a primary key field in the table street names? ... And if so is it a single field? ... GROUP BY, PostCode ... my SQL will only find duplicate Street Names, which will only be duplicate if ...
    (microsoft.public.access.queries)
  • Re: Bulk Update To Db Tables
    ... above that have lots of duplicate entries. ... I guess the Update Query would do the trick? ... As to whether to use append or update queries, it depends on whether you are ...
    (microsoft.public.access.tablesdbdesign)