Delete Query Error 3128 1

[puforee]

My DB is dependent on a linked spreadsheet to update a table. The spreadsheet is update weekly. The main table in my DB was created on the original data from the spreadsheet and with updates to the spreadsheet may need records added and/or removed. The addition part is easy. But the Deletion part is causing problems.

So, I created a compare query to determine which records need to be deleted. I then have another query that produces the final records/fields to be deleted (Not all fields from the spreadsheet are used).

I then attempted a delete query for the main table Where the items to be deleted are based on the final query (above)

Here is the code:

DELETE [Current_Quals_tbl Copy] AS Expr1, [Current_Quals_tbl Copy].[User Name], [Current_Quals_tbl Copy].Qualification, [Current_Quals_tbl Copy].[B1/AF], [Current_Quals_tbl Copy].[B2/AV], [Current_Quals_tbl Copy].[Issue Date], [Current_Quals_tbl Copy].[Expiration Date]
FROM [Current_Quals_tbl Copy] INNER JOIN Breakout_IPSS_Remove ON ([Current_Quals_tbl Copy].[User Name] = Breakout_IPSS_Remove.[User Name]) AND ([Current_Quals_tbl Copy].Qualification = Breakout_IPSS_Remove.Qualification) AND ([Current_Quals_tbl Copy].[B1/AF] = Breakout_IPSS_Remove.[B1/AF]) AND ([Current_Quals_tbl Copy].[B2/AV] = Breakout_IPSS_Remove.[B2/AV]) AND ([Current_Quals_tbl Copy].[Issue Date] = Breakout_IPSS_Remove.[Issue Date]) AND ([Current_Quals_tbl Copy].[Expiration Date] = Breakout_IPSS_Remove.[Expiration Date])
WHERE ((([Current_Quals_tbl Copy].[User Name])=[Breakout_IPSS_Remove]![User Name]) AND (([Current_Quals_tbl Copy].Qualification)=[Breakout_IPSS_Remove]![Qualification]) AND (([Current_Quals_tbl Copy].[B1/AF])=[Breakout_IPSS_Remove]![B1/AF]) AND (([Current_Quals_tbl Copy].[B2/AV])=[Breakout_IPSS_Remove]![B2/AV]) AND (([Current_Quals_tbl Copy].[Issue Date])=[Breakout_IPSS_Remove]![Issue Date]) AND (([Current_Quals_tbl Copy].[Expiration Date])=[Breakout_IPSS_Remove]![Expiration Date]));

I understand the select statement must identify the table that will have records deleted...google tells me this. I have tried several ways to redo the query without success.

Can anyone please show me how to format the Delete query correctly for this case. I don't work with delete queries very often.
Thanks,

 

[dhookom]

Your posting would be better if:
[ul]
[li]You told us which is your "main table"[/li]
[li]If you are getting an error or what your results are[/li]
[li]If you change the query to a select query, can you edit records[/li]
[li]Are any of the join fields part of a primary key/foreign key relationship[/li]
[li]Your post was formatted better[/li]
[/ul]
Here is your SQL with a little editing. I'm not sure why you duplicate your JOIN clause in your WHERE clause.

DELETE [Current_Quals_tbl Copy] AS Expr1, [Current_Quals_tbl Copy].[User Name], 
  [Current_Quals_tbl Copy].Qualification, [Current_Quals_tbl Copy].[B1/AF],
  [Current_Quals_tbl Copy].[B2/AV], [Current_Quals_tbl Copy].[Issue Date], 
  [Current_Quals_tbl Copy].[Expiration Date]
FROM [Current_Quals_tbl Copy] 
 INNER JOIN Breakout_IPSS_Remove ON 
   ([Current_Quals_tbl Copy].[User Name] = Breakout_IPSS_Remove.[User Name]) AND 
   ([Current_Quals_tbl Copy].Qualification = Breakout_IPSS_Remove.Qualification) AND 
   ([Current_Quals_tbl Copy].[B1/AF] = Breakout_IPSS_Remove.[B1/AF]) AND 
   ([Current_Quals_tbl Copy].[B2/AV] = Breakout_IPSS_Remove.[B2/AV]) AND 
   ([Current_Quals_tbl Copy].[Issue Date] = Breakout_IPSS_Remove.[Issue Date]) AND 
   ([Current_Quals_tbl Copy].[Expiration Date] = Breakout_IPSS_Remove.[Expiration Date])
WHERE ((([Current_Quals_tbl Copy].[User Name])=[Breakout_IPSS_Remove]![User Name]) AND
   (([Current_Quals_tbl Copy].Qualification)=[Breakout_IPSS_Remove]![Qualification]) AND
   (([Current_Quals_tbl Copy].[B1/AF])=[Breakout_IPSS_Remove]![B1/AF]) AND
   (([Current_Quals_tbl Copy].[B2/AV])=[Breakout_IPSS_Remove]![B2/AV]) AND
   (([Current_Quals_tbl Copy].[Issue Date])=[Breakout_IPSS_Remove]![Issue Date]) AND 
   (([Current_Quals_tbl Copy].[Expiration Date])=[Breakout_IPSS_Remove]![Expiration Date]));

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

When you do Delete, you don't Delete separate fields from the table, you Delete the entire record(s) from the table. That's why your basic Delete statement looks like:
[tt]
Delete From MyTableName
Where MyPK_Field = 123[/tt]

or to Delete multiple records, you can do:
[tt]
Delete From MyTableName
Where MyPK_Field IN ([blue]123, 456, 432, 456[/blue])[/tt]

I hope you do have a Primary Key field in the table where you want to Delete records.

So what I do is to create a Select statement that returns the list of PK's of the record(s) I want to Delete, and plug this Select statement in the [blue]BLUE[/blue] part of the above Delete statement.

So if this return the PK's of the records I want to Delete:[tt][blue]
Select PKField From ATable
Where SomeCriteris = Whatever[/blue][/tt]

My Delete statement would be
[tt]
Delete From MyTableName
Where MyPK_Field IN ([blue]
Select PKField From ATable
Where SomeCriteris = Whatever[/blue])
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Duane,
•Main Table: Current_Quals_tbl Copy
•Error: Data sheet view gives correct data. Run gives "Specify the table containing the records you want to delete" Help shows the Error Code shown in the forum title. 3128
•If you change the query to a select query, can you edit records NO
•Are any of the join fields part of a primary key/foreign key relationship. No and there is no primary key

I pasted your SQL into my Query. It produced a field Expr1:[Current_Quals_tbl Copy]. When I run the query it asks for a value for this field. I leave it blank and it shows the correct information in Data Sheet view except the Expr1 field is blank. And when I run the query it still asks for a table name.

 

[puforee]

Andy,
In the "Current_Quals_tbl Copy" table,(my main table for testing this), there is not primary key. It is created against a bulk download from another system..my MS Access. When the spreadsheet is analyzed and specific information is extracted and named, it creates the "Current_Quals_tbl Copy" table. If I created the data from scratch I would probably have more tables. A name table and a qualification table. There are many names and each can have many qualifications. But that does not happen because I am working with bulk data.

So, there are 6 fields that make the record unique. And there are 6 fields from a query that define what records to remove. How do I do this?. Should I add an auto number field to the "Current_Quals_tbl Copy" table and then use you Select PK From "Current_Quals_tbl Copy" statement to start?

And how would I write the where statement: the fields that have to be compared are listed in my code above:

Please show me how this can be coded... as you both can tell I am very weak on Delete queries. Appends are so much easier.

Thanks,

 

[puforee]

Andy,
..another system, not MS Access...

Sorry

 

[Andrzejek]

Is it correct to say that you want to Delete records from [tt][Current_Quals_tbl Copy][/tt] table that match records in [tt]Breakout_IPSS_Remove [/tt] table on fields: [User Name], Qualification, [B1/AF], [B2/AV], [Issue Date], and [Expiration Date] ?

If so, you may try something like:
[tt]
DELETE FROM [Current_Quals_tbl Copy]
WHERE [User Name] & Qualification & [B1/AF] & [B2/AV] & [Issue Date] & [Expiration Date] IN
(SELECT [User Name] & Qualification & [B1/AF] & [B2/AV] & [Issue Date] & [Expiration Date]
FROM Breakout_IPSS_Remove)[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Purforee,
I only copied your SQL and formatted it. Nothing else was changed so it would respond exactly the same as you provided.

I expect that if you can't edit records in the select query, you won't be able to delete records from the query. Typically the Access syntax would be:

DELETE [Current_Quals_tbl Copy].*
FROM...

I expect Andy's query could be edited and therefore allow deletes.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Andy,

Yes, it is correct.

But, when I paste your code into my query and run it..it says "Query must have at least one destination field"

What do you think?

 

[dhookom]

Try add the *

DELETE FROM [Current_Quals_tbl Copy].*
 WHERE [User Name] & Qualification & [B1/AF] & [B2/AV] & [Issue Date] & [Expiration Date] IN
 (SELECT [User Name] & Qualification & [B1/AF] & [B2/AV] & [Issue Date] & [Expiration Date]
 FROM Breakout_IPSS_Remove)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Or:[tt]
DELETE[highlight #FCE94F] * [/highlight]FROM [Current_Quals_tbl Copy]
WHERE ...[/tt]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy,

I ran this query:

DELETE * FROM [Current_Quals_tbl Copy] 
 WHERE [User Name] & Qualification & [B1/AF] & [B2/AV] & [Issue Date] & [Expiration Date] IN
 (SELECT [User Name] & Qualification & [B1/AF] & [B2/AV] & [Issue Date] & [Expiration Date]
 FROM Breakout_IPSS_Remove)

It works.

Thanks Andy

 

[Andrzejek]

I am glad

I hope not only "It works" but it works correctly

Have fun.

---- Andy

There is a great need for a sarcasm font.