Error in Delete Query

[djkc]

I made a query to check socials from an active tbl against socials from an
inactive table. I run the query with a left join so that both fields are
equal to show me all the inactive socials that are in the active tbl. I need
these deleted out of the inactive tbl so I change my query type from select
to delete and recieve the error specify the table you want the records to be
deleted from. Anyone know how to get around this?

 

[djkc]

I forgot to include the SQL view of my query:
DELETE [Paid out credex Accts].BranchNumber, [Paid out credex Accts].Name,
[Paid out credex Accts].SSN, [Paid out credex Accts].Acct, [Paid out credex
Accts].LoanClass, [Paid out credex Accts].Balance, [Paid out credex
Accts].Term, [Paid out credex Accts].RegularPayment, [Paid out credex
Accts].LoanDate, [Paid out credex Accts].FirstPaymentDate, [Paid out credex
Accts].StatusCode, [Paid out credex Accts].PledgeCode, [Paid out credex
Accts].ContrDelqCode, [Paid out credex Accts].[AD&DTerm], [Paid out credex
Accts].[AD&DPrem], [Paid out credex Accts].NonFileFee, [Paid out credex
Accts].Operator, [Paid out credex Accts].NextDueDate, [Paid out credex
Accts].LastPaymentDate, [Paid out credex Accts].AutoPrem
FROM [Open Credex Loans] INNER JOIN [Paid out credex Accts] ON [Open Credex
Loans].SSN = [Paid out credex Accts].SSN;

 

[John W. Vinson]

I made a query to check socials from an active tbl against socials from an
inactive table. I run the query with a left join so that both fields are
equal to show me all the inactive socials that are in the active tbl. I need
these deleted out of the inactive tbl so I change my query type from select
to delete and recieve the error specify the table you want the records to be
deleted from. Anyone know how to get around this?

A DELETE query needs to specify just the table from which you want to delete
records; you can use the * "all fields" option for this purpose. In the query
grid, you can just uncheck the Show checkbox on fields used only for criteria.
Try

DELETE [Paid out credex Accts].*
FROM [Open Credex Loans] INNER JOIN [Paid out credex Accts] ON [Open Credex
Loans].SSN = [Paid out credex Accts].SSN;

to delete all matching records in [Paid out credex Accts] if they have a match
(obviously, change the table name in the DELETE clause if you want to delete
records from [Open Credex Loans], and *MAKE A BACKUP FIRST* just in case!)

 

[djkc]

John:
Just go under the SQL view and add that to my query or make an entierly new
query and put that in the SQL View?

I made a query to check socials from an active tbl against socials from an
inactive table. I run the query with a left join so that both fields are
equal to show me all the inactive socials that are in the active tbl. I need
these deleted out of the inactive tbl so I change my query type from select
to delete and recieve the error specify the table you want the records to be
deleted from. Anyone know how to get around this?

A DELETE query needs to specify just the table from which you want to delete
records; you can use the * "all fields" option for this purpose. In the query
grid, you can just uncheck the Show checkbox on fields used only for criteria.
Try

DELETE [Paid out credex Accts].*
FROM [Open Credex Loans] INNER JOIN [Paid out credex Accts] ON [Open Credex
Loans].SSN = [Paid out credex Accts].SSN;

to delete all matching records in [Paid out credex Accts] if they have a match
(obviously, change the table name in the DELETE clause if you want to delete
records from [Open Credex Loans], and *MAKE A BACKUP FIRST* just in case!)

 

[djkc]

John,
I tried your SQL Statement in a new query and it is giving me a syntax error
and goes to the

Open Credex

Loans].SSN = [Paid out credex Accts].SSN;

part as where the error is.

I made a query to check socials from an active tbl against socials from an
inactive table. I run the query with a left join so that both fields are
equal to show me all the inactive socials that are in the active tbl. I need
these deleted out of the inactive tbl so I change my query type from select
to delete and recieve the error specify the table you want the records to be
deleted from. Anyone know how to get around this?

A DELETE query needs to specify just the table from which you want to delete
records; you can use the * "all fields" option for this purpose. In the query
grid, you can just uncheck the Show checkbox on fields used only for criteria.
Try

DELETE [Paid out credex Accts].*
FROM [Open Credex Loans] INNER JOIN [Paid out credex Accts] ON [Open Credex
Loans].SSN = [Paid out credex Accts].SSN;

to delete all matching records in [Paid out credex Accts] if they have a match
(obviously, change the table name in the DELETE clause if you want to delete
records from [Open Credex Loans], and *MAKE A BACKUP FIRST* just in case!)

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

The following is a query that should delete any matched records in [Paid out
credex Accts] table.

DELETE
FROM [Paid out credex Accts]
WHERE [Paid out credex Accts].SSN in
(SELECT [Open Credex Loans].SSN
FROM [Open Credex Loans])


**SOMETIMES** adding DISTINCTROW to the query will work
DELETE DISTINCTROW [Paid out credex Accts].BranchNumber
, [Paid out credex Accts].Name,
[Paid out credex Accts].SSN, [Paid out credex Accts].Acct
, [Paid out credex Accts].LoanClass, [Paid out credex Accts].Balance
, [Paid out credex Accts].Term, [Paid out credex Accts].RegularPayment
, [Paid out credex Accts].LoanDate, [Paid out credex Accts].FirstPaymentDate,
[Paid out credex Accts].StatusCode, [Paid out credex Accts].PledgeCode
, [Paid out credex Accts].ContrDelqCode, [Paid out credex Accts].[AD&DTerm],
[Paid out credex Accts].[AD&DPrem], [Paid out credex Accts].NonFileFee
, [Paid out credex Accts].Operator, [Paid out credex Accts].NextDueDate
, [Paid out credex Accts].LastPaymentDate, [Paid out credex Accts].AutoPrem
FROM [Open Credex Loans] INNER JOIN [Paid out credex Accts]
ON [Open Credex Loans].SSN = [Paid out credex Accts].SSN;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I forgot to include the SQL view of my query:
DELETE [Paid out credex Accts].BranchNumber, [Paid out credex Accts].Name,
[Paid out credex Accts].SSN, [Paid out credex Accts].Acct, [Paid out credex
Accts].LoanClass, [Paid out credex Accts].Balance, [Paid out credex
Accts].Term, [Paid out credex Accts].RegularPayment, [Paid out credex
Accts].LoanDate, [Paid out credex Accts].FirstPaymentDate, [Paid out credex
Accts].StatusCode, [Paid out credex Accts].PledgeCode, [Paid out credex
Accts].ContrDelqCode, [Paid out credex Accts].[AD&DTerm], [Paid out credex
Accts].[AD&DPrem], [Paid out credex Accts].NonFileFee, [Paid out credex
Accts].Operator, [Paid out credex Accts].NextDueDate, [Paid out credex
Accts].LastPaymentDate, [Paid out credex Accts].AutoPrem
FROM [Open Credex Loans] INNER JOIN [Paid out credex Accts] ON [Open Credex
Loans].SSN = [Paid out credex Accts].SSN;

I made a query to check socials from an active tbl against socials from an
inactive table. I run the query with a left join so that both fields are
equal to show me all the inactive socials that are in the active tbl. I need
these deleted out of the inactive tbl so I change my query type from select
to delete and recieve the error specify the table you want the records to be
deleted from. Anyone know how to get around this?

 

[John W. Vinson]

John,
I tried your SQL Statement in a new query and it is giving me a syntax error
and goes to the

Open Credex

Loans].SSN = [Paid out credex Accts].SSN;

part as where the error is.

I wonder if word wrap is causing a problem. Copy the SQL into the SQL window
of a new query and make sure it's all on one line (or that it word wraps only
where Access chooses to word wrap it). What you posted seems to be missing an
open square bracket before the [Open Credex Loans] but you clearly only posted
a portion - perhaps you could post the entire SQL string.

You *do* have a backup... right!?