Re: Duplicate Record Mess

Tech-Archive recommends: Speed Up your PC by fixing your registry

Michael

Let me try to paraphrase...

If you have more than one record in NAMES that match on whatever field(s),
you check another table (?HuskyTemp) to see if there's a copy there. If so,
you write ?all matching records to "DupeHusky".

If you have a match (one record) between NAMES and HuskyTemp, you (still)
want to write to DupeHusky.

There are other potential conditions -- are these not of concern? For
instance, if you have more than one (duplicate), but NO record in HuskyTemp,
what do you do?

Just some rambling thoughts...

* You might not need to write entire records if you have recordIDs -- just
keep the IDs
* Could you use a Find Duplicates query alone, instead of writing records?
* If it doesn't matter how many dupes in NAMES, just that there's a match
in HuskyTemp, why bother checking for dupes in NAMES?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MeloMike" <MeloMike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BB04A722-04E4-4CF8-83C5-77532D348644@xxxxxxxxxxxxxxxx
Thanks Jeff,

Here's the code and the problem now is:

If there is a "Jim Shoe" in the "HuskyTemp" table and a duplicate (2
records) of "Jim Shoe" in the "NAMES" table, the 2 records from the
"NAMES"
table will be written to the "DupeHusky" table.
BUT
If there is a "Jim Shoe" record in the "HuskyTemp" table and a duplicate
"Jim Shoe" record in the "NAMES" table it is _not_ written in the
"DupeHusky"
table like it should be.

SELECT NAMES.LNAME, NAMES.FNAME, NAMES.ADD1, NAMES.EMAIL, NAMES.ID,
NAMES.ENTRYDATE INTO DupeHusky
FROM [NAMES] INNER JOIN HuskyTemp ON (NAMES.EMAIL = HuskyTemp.Email) AND
(NAMES.ADD1 = HuskyTemp.ADD1) AND (NAMES.LNAME = HuskyTemp.LNAME) AND
(NAMES.FNAME = HuskyTemp.FNAME)
WHERE (((NAMES.LNAME) In (SELECT [LNAME] FROM [NAMES] As Tmp GROUP BY
[LNAME],[ADD1] HAVING Count(*)>1 And [ADD1] = [NAMES].[ADD1])) AND
((NAMES.FNAME) In (SELECT [FNAME] FROM [NAMES] As Tmp GROUP BY
[FNAME],[ADD1]
HAVING Count(*)>1 And [ADD1] = [NAMES].[ADD1])))
ORDER BY NAMES.LNAME, NAMES.ADD1;

Thanks for your time and help.
--
Peace,
Michael


"Jeff Boyce" wrote:

You can create a query that joins on all the fields you mentioned,
between
two tables. You can then click the SQL view button to view the SQL.

By the way, if John D. and his son John both live at the same address,
and
both use the family's email address, you'll be eliminating one of them...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MeloMike" <MeloMike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B25C82DC-5554-4054-AD3C-83EC05FB9151@xxxxxxxxxxxxxxxx
I'm working with a 75,000 record database in Access. The problem of
Duplicate
records has been going on for months and my task is to eliminate all
duplicates. I wrote a SQL querie that will search the "NAMES" table for
duplicates and write them to a seperate table. Works great ... only
18,000
duplicate records. :-/ Now I need to stop the entry of duplicate
records.
So
I need a query that will check a temperary table against the "NAMES"
table
and stop dupes from being added to "NAMES" table.

I first need to look at FNAME field and compare duplicates against
LNAME.
If
there are two John Does, I then need to look at the ADD1 field to see
if
the
John Doe has the same address (if so the records are written to a
"DUPE"
table) if address is different, I look at the EMAIL field. If there is
a
duplicat John Doe with a duplicate email address, it is written to the
"DUPE"
table.

The single table query looks like this:
INSERT INTO [Dupe]
SELECT NAMES.LNAME AS LNAME, NAMES.FNAME AS FNAME, NAMES.ADD1 AS ADD1,
NAMES.EMAIL AS EMAIL, NAMES.ID AS ID, NAMES.ENTRYDATE AS ENTRYDATE
FROM [NAMES]
WHERE ((([NAMES].[LNAME]) In (SELECT [LNAME] FROM [NAMES] As Tmp GROUP
BY
[LNAME],[ADD1] HAVING Count(*)>1 And [ADD1] = [NAMES].[ADD1]))) AND
((([NAMES].[FNAME]) In (SELECT [FNAME] FROM [NAMES] As Tmp GROUP BY
[FNAME],[ADD1] HAVING Count(*)>1 And [ADD1] = [NAMES].[ADD1])))
ORDER BY NAMES.LNAME, NAMES.Add1;

How do I write something that compare 2 tables in the same manner?

--
Peace,
Michael





.

Quantcast