delete query

[geebee]

hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those records
which have a [loan acct #] that exist in tbl_archive with today's date. i
accidentally erased all records from tbl_masterpop_main, so I really need to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee

 

[John Spencer]

Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have one
of the Loan Acct # or are there other criteria that should be used - such as
a date field in tbl_masterpop_main?

 

[geebee]

i want to delete the records from tbl_masterpop_main, but only if the [loan
acct #] also exists in tbl_archive and if the [popenterdt] = date()

thanks in advance,
geebee

Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have one
of the Loan Acct # or are there other criteria that should be used - such as
a date field in tbl_masterpop_main?

hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those records
which have a [loan acct #] that exist in tbl_archive with today's date. i
accidentally erased all records from tbl_masterpop_main, so I really need
to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee

 

[geebee]

oops. yes, your first query returns the right rcords to delete. now how do
i get this query to delete from tbl_masterpop_main those same records which
have a [loan acct #]s of the first query, and with [popenterdt] = date()

thanks in advance,
geebee

Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have one
of the Loan Acct # or are there other criteria that should be used - such as
a date field in tbl_masterpop_main?

hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those records
which have a [loan acct #] that exist in tbl_archive with today's date. i
accidentally erased all records from tbl_masterpop_main, so I really need
to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee

 

[John Spencer]

So is PopEnterDt in the main query also?

DELETE DistinctRow tbl_masterpop_main.[Loan Acct #]
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date())

If tbl_masterpop_main has a field popenterdt and you only want to delete
records that have today's date in addition to the loan Acct # being in
tbl_archive then you need to add the following to the end of the query (not
as part of the subquery)

AND tbl_masterpop_main.[popenterdt] = date()

oops. yes, your first query returns the right rcords to delete. now how
do
i get this query to delete from tbl_masterpop_main those same records
which
have a [loan acct #]s of the first query, and with [popenterdt] = date()

thanks in advance,
geebee

Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have
one
of the Loan Acct # or are there other criteria that should be used - such
as
a date field in tbl_masterpop_main?

hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those
records
which have a [loan acct #] that exist in tbl_archive with today's date.
i
accidentally erased all records from tbl_masterpop_main, so I really
need
to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee