How to specify table to delete records.

[AndrewB]

Hi,

Below is a simple Delete Query I am having problems with. I want to delete
all records in Table: [SAART BTN'S WITHOUT TIECODE DETAIL] where field
[BTN10] matches field [BTN10] in Table: [Master List]. I get the Access
error msg below:

Specify the table containing the records you want to delete. (Error 3128)

I do not know where or how to specify my table. The records in the table
are already in the query body and the "Select Query" for this works great. I
am a little lost here..... "Help" ) My query is below.

DELETE [SAART BTN'S WITHOUT TIECODE DETAIL].CUSTOMER, [SAART BTN'S WITHOUT
TIECODE DETAIL].BTN10, [SAART BTN'S WITHOUT TIECODE DETAIL].AGID, [SAART
BTN'S WITHOUT TIECODE DETAIL].PID, [SAART BTN'S WITHOUT TIECODE DETAIL].CID,
[SAART BTN'S WITHOUT TIECODE DETAIL].BLR_ID, [SAART BTN'S WITHOUT TIECODE
DETAIL].[Date Added], [SAART BTN'S WITHOUT TIECODE
DETAIL].[SuperSessioned-2-Master], [SAART BTN'S WITHOUT TIECODE DETAIL].[AREA
CODE]
FROM [SAART BTN'S WITHOUT TIECODE DETAIL] INNER JOIN [MASTER LIST] ON [SAART
BTN'S WITHOUT TIECODE DETAIL].BTN10 = [MASTER LIST].BTN10;

 

[Marshall Barton]

Below is a simple Delete Query I am having problems with. I want to delete
all records in Table: [SAART BTN'S WITHOUT TIECODE DETAIL] where field
[BTN10] matches field [BTN10] in Table: [Master List]. I get the Access
error msg below:

Specify the table containing the records you want to delete. (Error 3128)

I do not know where or how to specify my table. The records in the table
are already in the query body and the "Select Query" for this works great. I
am a little lost here..... "Help" ) My query is below.

DELETE [SAART BTN'S WITHOUT TIECODE DETAIL].CUSTOMER, [SAART BTN'S WITHOUT
TIECODE DETAIL].BTN10, [SAART BTN'S WITHOUT TIECODE DETAIL].AGID, [SAART
BTN'S WITHOUT TIECODE DETAIL].PID, [SAART BTN'S WITHOUT TIECODE DETAIL].CID,
[SAART BTN'S WITHOUT TIECODE DETAIL].BLR_ID, [SAART BTN'S WITHOUT TIECODE
DETAIL].[Date Added], [SAART BTN'S WITHOUT TIECODE
DETAIL].[SuperSessioned-2-Master], [SAART BTN'S WITHOUT TIECODE DETAIL].[AREA
CODE]
FROM [SAART BTN'S WITHOUT TIECODE DETAIL] INNER JOIN [MASTER LIST] ON [SAART
BTN'S WITHOUT TIECODE DETAIL].BTN10 = [MASTER LIST].BTN10;

The syntax for Delete queries requires the name of the table
right after the word DELETE

DELETE [SAART BTN'S WITHOUT TIECODE DETAIL].*
FROM . . .

Make sure you have a good backup copy before testing this
kind of thing.

 

[AndrewB]

--
AndrewB.

Below is a simple Delete Query I am having problems with. I want to delete
all records in Table: [SAART BTN'S WITHOUT TIECODE DETAIL] where field
[BTN10] matches field [BTN10] in Table: [Master List]. I get the Access
error msg below:

Specify the table containing the records you want to delete. (Error 3128)

I do not know where or how to specify my table. The records in the table
are already in the query body and the "Select Query" for this works great. I
am a little lost here..... "Help" ) My query is below.

DELETE [SAART BTN'S WITHOUT TIECODE DETAIL].CUSTOMER, [SAART BTN'S WITHOUT
TIECODE DETAIL].BTN10, [SAART BTN'S WITHOUT TIECODE DETAIL].AGID, [SAART
BTN'S WITHOUT TIECODE DETAIL].PID, [SAART BTN'S WITHOUT TIECODE DETAIL].CID,
[SAART BTN'S WITHOUT TIECODE DETAIL].BLR_ID, [SAART BTN'S WITHOUT TIECODE
DETAIL].[Date Added], [SAART BTN'S WITHOUT TIECODE
DETAIL].[SuperSessioned-2-Master], [SAART BTN'S WITHOUT TIECODE DETAIL].[AREA
CODE]
FROM [SAART BTN'S WITHOUT TIECODE DETAIL] INNER JOIN [MASTER LIST] ON [SAART
BTN'S WITHOUT TIECODE DETAIL].BTN10 = [MASTER LIST].BTN10;

The syntax for Delete queries requires the name of the table
right after the word DELETE

DELETE [SAART BTN'S WITHOUT TIECODE DETAIL].*
FROM . . .

Make sure you have a good backup copy before testing this
kind of thing.

Hi Marsh,

I am still not understanding. I simplified the query to only 1 field just
to experiment.

DELETE [Test] BTN10
FROM [Test] INNER JOIN [MASTER LIST] ON [Test].BTN10 = [MASTER LIST].BTN10;

Would you format this query so it will delete the record "BTN10" from table
[Test] ? As an fyi, Table [Test] and table [Master List] have a 1 to 1
relation at field "BTN10"

I really appreciate your help

 

[Marshall Barton]

I am still not understanding. I simplified the query to only 1 field just
to experiment.

DELETE [Test] BTN10
FROM [Test] INNER JOIN [MASTER LIST] ON [Test].BTN10 = [MASTER LIST].BTN10;

Would you format this query so it will delete the record "BTN10" from table
[Test] ? As an fyi, Table [Test] and table [Master List] have a 1 to 1
relation at field "BTN10"

What you posted is missing the dot in the first line.

The field in the first line has no meaning so the standard
syntax is to use an asterick:

DELETE [Test].*
FROM . . .

 

[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.

DELETE
FROM [SAART BTN'S WITHOUT TIECODE DETAIL]
WHERE BTN10 IN (SELECT BTN10 FROM [Master List])


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