Specify the table containing the records you want to delete (1 Viewer)

NearImpossible

Registered User.
Local time
Today, 11:33
Joined
Jul 12, 2019
Messages
186
I keep the the above error when running the following query

SQL:
DELETE db_TempAddEquipmentToTicket.TicketID, db_TempAddEquipmentToTicket.EquipmentID, db_TempAddEquipmentToTicket.EquipmentCategory, db_TempAddEquipmentToTicket.Equipment, db_TempAddEquipmentToTicket.EquipmentName, db_TempAddEquipmentToTicket.Location, db_TempAddEquipmentToTicket.[Make / Model], db_TempAddEquipmentToTicket.[IP Address], db_TempAddEquipmentToTicket.[Serial Number], db_TempAddEquipmentToTicket.[Asset Tag], db_TempAddEquipmentToTicket.[Equipment Transfer], db_TempAddEquipmentToTicket.[End Facility], db_TempAddEquipmentToTicket.EndFacilityID, db_TempAddEquipmentToTicket.[RMA Transfer], db_TempAddEquipmentToTicket.[RMA ID], db_TempAddEquipmentToTicket.[RMA Complete], db_TempAddEquipmentToTicket.[RMA Hold]
FROM db_TempAddEquipmentToTicket INNER JOIN db_AddTicketEquipment ON db_TempAddEquipmentToTicket.TicketID = db_AddTicketEquipment.TicketID
WHERE ((db_TempAddEquipmentToTicket!EquipmentID=db_AddTicketEquipment!EquipmentID) And (db_TempAddEquipmentToTicket![Equipment Transfer]=db_AddTicketEquipment![Ticket Select]));
I'm sure its something simple and i'm just missing it, any thoughts??

thanks in advanced
Kevin
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 19, 2002
Messages
29,167
When you issue a delete on a join of two tables, the delete is always for the "child" table. I'm guessing that this is what you want.
Code:
DELETE db_AddTicketEquipment.*
FROM db_TempAddEquipmentToTicket INNER JOIN db_AddTicketEquipment ON db_TempAddEquipmentToTicket.TicketID = db_AddTicketEquipment.TicketID
WHERE ((db_TempAddEquipmentToTicket!EquipmentID=db_AddTicketEquipment!EquipmentID) And (db_TempAddEquipmentToTicket![Equipment Transfer]=db_AddTicketEquipment![Ticket Select]));
 

NearImpossible

Registered User.
Local time
Today, 11:33
Joined
Jul 12, 2019
Messages
186
After trying your recommendation, I am now getting the following error:

Invalid SQL statement; expected 'DELETE', 'INSERT', PROCEDURE', SELECT, or 'UPDATE'

I appreciate your help, but after a little more thought on this, I've decided to scrap this portion of my database and attack it in a different route.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 19, 2002
Messages
29,167
When you are not familiar with SQL syntax, use the QBE to build your queries. Start with the select part. When that works to select the records you want to take action on, change the query to delete, update, append as needed.
 

NearImpossible

Registered User.
Local time
Today, 11:33
Joined
Jul 12, 2019
Messages
186
When you are not familiar with SQL syntax, use the QBE to build your queries. Start with the select part. When that works to select the records you want to take action on, change the query to delete, update, append as needed.
Actually I used the Access Query builder to make the select statement, which worked, so I changed it to a Delete query, and then that is when I got the "Specify the table...." error

I like to use the Access Query builder to create the query, then switch it to SQL view to see the actual code and learn how it should be laid out so the above code was posted from the Access SQL view vs using a SQL Pass through Query.

I realized the way I was going about trying to accomplish what I wanted wasn't the most practical, so I went back to the drawing board and now have it working in a more efficient manner.

Thank you for your time and advice
Kevin
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 19, 2002
Messages
29,167
Delete queries do not delete individidual fields. They delete rows from a table so the "select" references tablename.*

Glad you've got it worked out.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom