How to use a Delete Query?

[bytes access nubie]

I have a query that links a table (static, no relationships) to a union query. I want to delete the matching records from the table. When I run the query the message displays: Could not delete from specified tables.

In the query, I dragged the asterisk, *, down rather thena the individual fields. also, i right clicked in the table area & selected Properties, Unique Records-Yes.

This DOES work when i have 2 tables linked in the query, rather than a table and a query. Am I missing something here?

A workaround is to create a new table out of the union query then create the delete query off of the 2 tables but I'd rather not do that since this will be performed on regular basis.

[Stewart Ross]

Hi. Problem is the join to a Union query, as you surmised. Access DELETE queries are quite restrictive about what they are joined to, and sometimes the use of a make-table query to generate a temporary table is necessary as you suggest.

There is an easy way to continue using your union query without generating an intermediate table, which is by using the union query as a subquery rather than joining it. The drawback of this approach is that it will be slower than the join method could have been, but it will definitely work.

You don't tell us the names of the table, the query and the joined fields, so in the skeleton SQL below I use generic names which you would need to replace with actuals. You can enter the SQL by replacing the existing SQL of your delete query in SQL view instead of Design view.

DELETE * 
FROM   [YourTable] 
WHERE  [YourTable].[FieldThatWasJoined]
IN     (SELECT [FieldThatWasJoined] from [YourUnionQuery]);

-Stewart