delete query

G

Guest

hi,

I have a table in which there appears to be duplicate and redundant records.
Can anyone tell me how I would write a delete wuery to delete those records
which are the duplicates? Make sure that we only delete the extra duplicates
for each [date].

sample tbl_archive data:

Date Loan Acct # PopEnterDt PrevStatus Status
6/26/2006 8267851001 6/1/2006 PDB1-30 PDC31-60
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDB1-30 PDC31-60

Here are the 2 queries that put the data into the table:
INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], [Datex] AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));


INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Buying Center #], [Short Name], [Loan Status], [Contract Date], [Mat
date], [Next Pmt Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts
Made], [# Reg Pmts Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date],
[Last Pmt Amt], [Amended?], [Amended This Mo?], [DEL Group], [Days
Delinquent], [Tot Past Due Pmts $], [Principal Balance], [Val Reserve Dollar
Amt], NETBAL, [Ext?], [Extended This Mo?], [Payoff Dollar Amt], [Flat Void
Mth of Orig], [Active BK Flag Date], [Active BK Flag], [Inventory Status],
[Charge Off Principal], [Chg Off Type], [CHG OFF Date], [Cure Letter Date],
[Skip Active Date], [Skip Completion Date], [Skip Completion Sts], [Redeemed
Date], [Entered Date], [Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT MasterPop.[Loan Acct #], [Datex] AS Expr1,
[1_Stg_WBStatus_Flag].PopEnterDt, [1_Stg_WBStatus_Flag].Status,
[1_Stg_WBStatus_Flag].WBStatus, MasterPop.[Buying Center #], MasterPop.[Short
Name], MasterPop.[Loan Status], MasterPop.[Contract Date], MasterPop.[Mat
date], MasterPop.[Next Pmt Due Date], MasterPop.[Payoff Date],
MasterPop.[Paid Off?], MasterPop.[Nbr of Reg Pmts Made], MasterPop.[# Reg
Pmts Remaining], MasterPop.[Pmt Scd Monthly Pmt Amt], MasterPop.[Last Pmt
Date], MasterPop.[Last Pmt Amt], MasterPop.[Amended?], MasterPop.[Amended
This Mo?], MasterPop.[DEL Group], MasterPop.[Days Delinquent], MasterPop.[Tot
Past Due Pmts $], MasterPop.[Principal Balance], MasterPop.[Val Reserve
Dollar Amt], MasterPop.NETBAL, MasterPop.[Ext?], MasterPop.[Extended This
Mo?], MasterPop.[Payoff Dollar Amt], MasterPop.[Flat Void Mth of Orig],
MasterPop.[Active BK Flag Date], MasterPop.[Active BK Flag Date],
MasterPop.[Inventory Status], MasterPop.[Charge Off Principal],
MasterPop.[Chg Off Type], MasterPop.[CHG OFF Date], MasterPop.[Cure Letter
Date], MasterPop.[Skip Active Date], MasterPop.[Skip Completion Date],
MasterPop.[Skip Completion Sts], MasterPop.[Redeemed Date],
MasterPop.[Entered Date], MasterPop.[Repo Ordered Date], MasterPop.[Active BK
Flag Date]
FROM (1_Stg_WBStatus_Flag INNER JOIN MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

Also, does anyone have any suggestions as to how to modify the query to
prevent duplicate rows for the combination of [loan acct #], [date] and
[status]?

Thanks in advance,
geebee
 
G

Guest

First make a complete backup of the database and put it away for safe keeping.

I'm assuming that you don't have Referiential Integrity on this table in the
Relationship window.
Create a query that contains all the fields in the table and make it a
distinct query.
SELECT DISTINCT * FROM tbl_archive ;

Run it and make sure that the results look right. If so make it a Make Table
query like so abd run it:
SELECT DISTINCT * INTO tbl_archive2 FROM tbl_archive ;

This will create tbl_archive2 with just unique records.
Delete tbl_archive.
Rename tbl_archive2 to tbl_archive.

To prevent this from happening again, make the combination of combination of
combination of [loan acct #], [date] and [status] a unique index or even
primary key. This will prevent your queries from creating duplicate data.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


geebee said:
hi,

I have a table in which there appears to be duplicate and redundant records.
Can anyone tell me how I would write a delete wuery to delete those records
which are the duplicates? Make sure that we only delete the extra duplicates
for each [date].

sample tbl_archive data:

Date Loan Acct # PopEnterDt PrevStatus Status
6/26/2006 8267851001 6/1/2006 PDB1-30 PDC31-60
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDC31-60 PDB1-30
6/26/2006 8267851001 6/1/2006 PDB1-30 PDC31-60

Here are the 2 queries that put the data into the table:
INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], [Datex] AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));


INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Buying Center #], [Short Name], [Loan Status], [Contract Date], [Mat
date], [Next Pmt Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts
Made], [# Reg Pmts Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date],
[Last Pmt Amt], [Amended?], [Amended This Mo?], [DEL Group], [Days
Delinquent], [Tot Past Due Pmts $], [Principal Balance], [Val Reserve Dollar
Amt], NETBAL, [Ext?], [Extended This Mo?], [Payoff Dollar Amt], [Flat Void
Mth of Orig], [Active BK Flag Date], [Active BK Flag], [Inventory Status],
[Charge Off Principal], [Chg Off Type], [CHG OFF Date], [Cure Letter Date],
[Skip Active Date], [Skip Completion Date], [Skip Completion Sts], [Redeemed
Date], [Entered Date], [Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT MasterPop.[Loan Acct #], [Datex] AS Expr1,
[1_Stg_WBStatus_Flag].PopEnterDt, [1_Stg_WBStatus_Flag].Status,
[1_Stg_WBStatus_Flag].WBStatus, MasterPop.[Buying Center #], MasterPop.[Short
Name], MasterPop.[Loan Status], MasterPop.[Contract Date], MasterPop.[Mat
date], MasterPop.[Next Pmt Due Date], MasterPop.[Payoff Date],
MasterPop.[Paid Off?], MasterPop.[Nbr of Reg Pmts Made], MasterPop.[# Reg
Pmts Remaining], MasterPop.[Pmt Scd Monthly Pmt Amt], MasterPop.[Last Pmt
Date], MasterPop.[Last Pmt Amt], MasterPop.[Amended?], MasterPop.[Amended
This Mo?], MasterPop.[DEL Group], MasterPop.[Days Delinquent], MasterPop.[Tot
Past Due Pmts $], MasterPop.[Principal Balance], MasterPop.[Val Reserve
Dollar Amt], MasterPop.NETBAL, MasterPop.[Ext?], MasterPop.[Extended This
Mo?], MasterPop.[Payoff Dollar Amt], MasterPop.[Flat Void Mth of Orig],
MasterPop.[Active BK Flag Date], MasterPop.[Active BK Flag Date],
MasterPop.[Inventory Status], MasterPop.[Charge Off Principal],
MasterPop.[Chg Off Type], MasterPop.[CHG OFF Date], MasterPop.[Cure Letter
Date], MasterPop.[Skip Active Date], MasterPop.[Skip Completion Date],
MasterPop.[Skip Completion Sts], MasterPop.[Redeemed Date],
MasterPop.[Entered Date], MasterPop.[Repo Ordered Date], MasterPop.[Active BK
Flag Date]
FROM (1_Stg_WBStatus_Flag INNER JOIN MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

Also, does anyone have any suggestions as to how to modify the query to
prevent duplicate rows for the combination of [loan acct #], [date] and
[status]?

Thanks in advance,
geebee
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combine 2 access queries 3
query speed 5
join type not supported 5
Query Error 2
duplicate records 3
DUPLICATE QUERY results 1
unwanted duplicate loan account numbers 1
runtime error 3001 invalid argument error message 2

Top