Specify the table containing the records you want to delete

[NearImpossible]

I keep the the above error when running the following query

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]

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.

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]

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]

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]

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]

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.