Delete Query based on records in a linked Excel table

[gaker10]

I have an Add and Update Query for this. Now I need a delete query.



I need my delete query to delete entire records based on matching between a Field1 in my linked excel table, and a Field11 in my database. Don't know why I can't seem to find this anywhere that does not involve the use of VBA.

[ranman256]

Use the query wizard and make the FIND DUPLICATES IN TABLE query.
Look at the design (as an example),
Using your example, You want to delete records in the Field1 so you must use a 'sub-query' using the IN statement in the field criteria.

Do the same for DELETE query.
delete * from table, where [field1] (in select [field1] from table2)

[gaker10]

Is that a criteria expression or a VBA expression? "delete * from table, where [field1] (in select [field1] from table2)"

[ranman256]

Youll understand it from the FIND DUPLICATES query.
Query only...NO vba needed.

[gaker10]

I don't see the connection between the DUPS query and the delete query, other than finding matches.

[ranman256]

The IN STATEMENT in the sql inside the field you want to match.
A query in a query.

(tho yours will be a DELETE not select)
The IN qry will be the list of items you want to remove
The DEL qry delete them.

[gaker10]

I'm not even sure how to set up the query before the criteria part though. Is this right so far?

[ranman256]

Thats a start, (you need 2 queries)
now you need to filter the records you get the list of what to delete. (in criteria)
save this query as qsRecs2Del

Then start a new query on the table you want to delete
pull down the field you want to MATCH to the query above that has the list to delete.
in the critieria , type: IN (select [field] from qsRecs2Del)
call this qry qdDelRecs


Its EXACLTY like the 'find dupes' example, but instead of find, change it to a delete qry.

[gaker10]

Oh I see now, just from your statements. The "from" table is actually the result of a matching query, and so I was looking for a reference to a table, not a query, hehe.

[gaker10]

I did what you suggested and I get the message: Specify the table containing the records you want to delete. Here's my query criteria:

[ranman256]

Is there a query called "Filter"? (see your IN statement)
It needs to be a table or query

Best practice is to name all tables start w T
reports w R
etc.

all queries starting with Q

qs.. select query
qu.. update
qa.. append
qm.. make table
qx.. cross tab
qd.. delete
qn.. union query

[gaker10]

That's the matching query: