Deleting Duplicate records from two tables

[hughess7]

Hi all

Each month I have to import customer data to a database. The table is keyed
on NEClaimNo and does not allow duplicates.

I have two tables, [tbl Warranty Data] which contains ALL past claims and
[Warranty Data] which holds the current month of claims to import.

The problem I have is that the customer issues credits and when this happens
I need to remove the original claim from [tbl Warranty Data] and not import
the credit from [Warranty Data].

I have queries to find the duplicates but I then delete the records manually
as there is usually just a few. This month there are 127 dups! Too many for
me to do manually and I can not delete them using the query as I get an error
'can not delete from specified table'. I assume it is because the query finds
the two dups from a match between two tables.

Can anyone tell me an easy way round this? Hope it is clear from the above
what I am trying to do... just ask if not...

Thanks in advance for any help.
Sue

 

[Jerry Whittle]

I'm guess that your query to find the dupes is a join between the two tables.
To do a delete from one table you probably need a sub-query to do the job.

Post the SQL that you use to find the dupes and maybe we can help you create
a useable delete query from it.

Whatever you do, test on a copy of the tables or entire database!

 

[hughess7]

Hi Jerry

Thanks!

Yes you are correct in your assumption. For reference I managed to delete
from one side of the table ok by copying to another table and adding the
primary key to this, that then allowed me to use this query to delete from
this table. But it still wouldn't let me delete from the other table. Problem
then being of course I'd lost my records in one table to match up to the
other table again ;-).

Luckily I am a good girl and I always take a copy of our customer databases
before doing the monthly importing routines .

After a lot of fiddling, creating more temp tables and queries etc, can't
remember how now though and it was very complicated, I finally achieved what
I wanted. I would appreciate you having a look at the sql for me though and
seeing if it is possible to simplify this for the next time it happens again!
No rush though thanks as it won't be for another month at least now...

SQL is:

SELECT [Warranty Data].Dealer, [Warranty Data].[Claim No], [Warranty
Data].[Invoice Date], [Warranty Data].[Allowed Parts Amount], [tbl Warranty
Data].[Total Parts Value], [Warranty Data].[Allowed Labor Amount], [tbl
Warranty Data].[Total Labour Value], [Warranty Data].[Allowed Sublet Amount],
[tbl Warranty Data].[Total Sublet Value], [tbl Warranty Data].[Self Billing
Date]
FROM [Warranty Data] INNER JOIN [tbl Warranty Data] ON ([Warranty
Data].[Claim No] = [tbl Warranty Data].[Claim No]) AND ([Warranty
Data].Dealer = [tbl Warranty Data].[Dealer No]);

Thanks again, most appreciated.
Sue

I'm guess that your query to find the dupes is a join between the two tables.
To do a delete from one table you probably need a sub-query to do the job.

Post the SQL that you use to find the dupes and maybe we can help you create
a useable delete query from it.

Whatever you do, test on a copy of the tables or entire database!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi all

Each month I have to import customer data to a database. The table is keyed
on NEClaimNo and does not allow duplicates.

I have two tables, [tbl Warranty Data] which contains ALL past claims and
[Warranty Data] which holds the current month of claims to import.

The problem I have is that the customer issues credits and when this happens
I need to remove the original claim from [tbl Warranty Data] and not import
the credit from [Warranty Data].

I have queries to find the duplicates but I then delete the records manually
as there is usually just a few. This month there are 127 dups! Too many for
me to do manually and I can not delete them using the query as I get an error
'can not delete from specified table'. I assume it is because the query finds
the two dups from a match between two tables.

Can anyone tell me an easy way round this? Hope it is clear from the above
what I am trying to do... just ask if not...

Thanks in advance for any help.
Sue