"Specify the table containing the records you want to delete" error

[msanewbie]

I read a few threads regarding this but I am unable to find a solution. I have two tables which I am using a delete query to remove data from. The first table 'SO' is my master table and 'SOupdate' is the table where I have data I want to remove from 'SO'.

My delete query is as follows (SQL view)

DELETE SO.[Security ], SO.Cusip, SO.[Security Description], SO.Holders, SO.Account, SO.[Exempt Code], SO.[Settle Date], SO.[Entry Date], SO.Amount, SO.[Rec ID ], SO.[Stat CD], SO.[Recl Stat], SO.[Div Typ], *
FROM SO INNER JOIN SOupdate ON (SO.Amount = SOupdate.Amount) AND (SO.[Entry Date] = SOupdate.[Entry Date]) AND (SO.[Settle Date] = SOupdate.[Settle Date]) AND (SO.Account = SOupdate.Account) AND (SO.Cusip = SOupdate.Cusip) AND (SO.[Security ] = SOupdate.[Security ]);

When I run it I get the dreaded error in subject. It is interesting as I have never encountered this issue before.

 

[boblarson]

You don't specify the fields to delete because a delete query deletes the entire row.

So, something like this (if all else is correct):

DELETE *
FROM SO INNER JOIN SOupdate ON (SO.Amount = SOupdate.Amount) AND (SO.[Entry Date] = SOupdate.[Entry Date]) AND (SO.[Settle Date] = SOupdate.[Settle Date]) AND (SO.Account = SOupdate.Account) AND (SO.Cusip = SOupdate.Cusip) AND (SO.[Security ] = SOupdate.[Security ]);

 

[msanewbie]

Unfortunately, that didn't work. Same error message. I am basically trying to delete the records from the SO table, which are present in SOupdate table. I am using this query as an undo function on a form. SOupdate was populated with data from a file and in the event the user accidently runs the update process twice I am using this query to undo that update. I thought by joining SO and SOupdate (using fields in common) and making it a delete query it would do the trick. But unfortunately it didn't.

You don't specify the fields to delete because a delete query deletes the entire row.

So, something like this (if all else is correct):

DELETE *
FROM SO INNER JOIN SOupdate ON (SO.Amount = SOupdate.Amount) AND (SO.[Entry Date] = SOupdate.[Entry Date]) AND (SO.[Settle Date] = SOupdate.[Settle Date]) AND (SO.Account = SOupdate.Account) AND (SO.Cusip = SOupdate.Cusip) AND (SO.[Security ] = SOupdate.[Security ]);

 

[boblarson]

Okay, do this -

Make the query a select query instead and save it. Then create a new query and use that one and specifiy

Delete * From QueryNameHere

That should do it.

 

[msanewbie]

Now I get the following error "Could not delete from specified tables"

My new delete query with sql view

DELETE *
FROM undoSO;

undoSO was my previous delete query

 

[boblarson]

Now I get the following error "Could not delete from specified tables"

My new delete query with sql view

DELETE *
FROM undoSO;

undoSO was my previous delete query

In your other query you didn't put any of the fields from the linked tables into the grid did you? If so and you just need them for criteria make sure the checkbox is not checked for display.

 

[msanewbie]

In your other query you didn't put any of the fields from the linked tables into the grid did you? If so and you just need them for criteria make sure the checkbox is not checked for display.

In my linked table which is now a query, when I uncheck the fields or remove them when I run the delete query I get the following message "Query must have at least one destination field."

 

[boblarson]

In my linked table which is now a query, when I uncheck the fields or remove them when I run the delete query I get the following message "Query must have at least one destination field."

The ONLY thing (other than criteria fields from the other tables - and unchecked) that should be in the grid is the asterisk *

so if you drag and drop the asterisk from the table list of fields for the table you are deleting from to the grid.

It would be something like this

FIELD: YourTableName.*
TABLE: YourTableName

 

[msanewbie]

The ONLY thing (other than criteria fields from the other tables - and unchecked) that should be in the grid is the asterisk *

so if you drag and drop the asterisk from the table list of fields for the table you are deleting from to the grid.

It would be something like this

FIELD: YourTableName.*
TABLE: YourTableName

Now I go back to the "Could not delete from specified tables"

Here are the two sql views

1) select query 'undoSO'
SELECT SO.*
FROM SO INNER JOIN SOupdate ON (SO.[Security ] = SOupdate.[Security ]) AND (SO.Cusip = SOupdate.Cusip) AND (SO.Account = SOupdate.Account) AND (SO.[Settle Date] = SOupdate.[Settle Date]) AND (SO.[Entry Date] = SOupdate.[Entry Date]) AND (SO.Amount = SOupdate.Amount);

2) Delete query 'delundoSO'
DELETE *
FROM undoSO;

 

[msanewbie]

Now I go back to the "Could not delete from specified tables"

Here are the two sql views

1) select query 'undoSO'
SELECT SO.*
FROM SO INNER JOIN SOupdate ON (SO.[Security ] = SOupdate.[Security ]) AND (SO.Cusip = SOupdate.Cusip) AND (SO.Account = SOupdate.Account) AND (SO.[Settle Date] = SOupdate.[Settle Date]) AND (SO.[Entry Date] = SOupdate.[Entry Date]) AND (SO.Amount = SOupdate.Amount);

2) Delete query 'delundoSO'
DELETE *
FROM undoSO;

Finally figured it out. I found the solution using microsoft's support page.

I had to switch Unique Records from No to Yes.

I read this somewhere else, but for whatever reason I missed it. Here is my final sql view. I actually reverted to my original delete query and it worked. Thanks for the help!

DELETE DISTINCTROW SO.*
FROM SO INNER JOIN SOupdate ON (SO.[Security ] = SOupdate.[Security ]) AND (SO.Cusip = SOupdate.Cusip) AND (SO.Account = SOupdate.Account) AND (SO.[Settle Date] = SOupdate.[Settle Date]) AND (SO.[Entry Date] = SOupdate.[Entry Date]) AND (SO.Amount = SOupdate.Amount);