INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Delete Query Error 3128

Delete Query Error 3128

(OP)
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:

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,

RE: Delete Query Error 3128

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

CODE --> sql

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

RE: Delete Query Error 3128

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:

Delete From MyTableName
Where MyPK_Field = 123


or to Delete multiple records, you can do:

Delete From MyTableName
Where MyPK_Field IN (123, 456, 432, 456)


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 part of the above Delete statement.

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


My Delete statement would be

Delete From MyTableName
Where MyPK_Field IN (
Select PKField From ATable
Where SomeCriteris = Whatever
)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Delete Query Error 3128

(OP)
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.

RE: Delete Query Error 3128

(OP)
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,

RE: Delete Query Error 3128

(OP)
Andy,
..another system, not MS Access...

Sorry

RE: Delete Query Error 3128

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

If so, you may try something like:

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)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Delete Query Error 3128

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:

CODE --> SQL

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

RE: Delete Query Error 3128

(OP)
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?

RE: Delete Query Error 3128

Try add the *

CODE --> vba

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

RE: Delete Query Error 3128

Or:
DELETE * FROM [Current_Quals_tbl Copy]
WHERE ...

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Delete Query Error 3128

(OP)
Andy,

I ran this query:

CODE -->

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

RE: Delete Query Error 3128

I am glad smile

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close