0

I am using a SELECT query in MS Access to select data from two tables.

In a second step I want to delete entries from a third table that are already displayed in the results of that select query. Therefore I wrote a DELETE query that deletes all records from the third table that show up in the result of the SELECT query.

Simple question: Is it enough to run the delete query from VBA code or do I need to run the SELECT query before, to get up-to-date results?

Table 1 and 2 are linked tables and they are updated daily, so the results of the SELECT query should differ, too, before I run the DELETE query.

Thanks in advance!

Update: It is two different queries, not a SELECT statement within a delete query.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
Nicole
  • 19
  • 1
  • Does this answer your question? [How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?](https://stackoverflow.com/questions/17548751/how-to-write-a-sql-delete-statement-with-a-select-statement-in-the-where-clause) – computercarguy Jan 03 '20 at 16:46
  • It doesn't, to be honest. It looks like they had a select statement within the delete query whereas I have two separate queries. I am quite new to MS Access and have never worked with SQL so maybe I just don't get the point... – Nicole Jan 03 '20 at 17:33
  • The point is to make a single query that does everything you want in essentially one step. Then you don't have to worry about referencing one query from another or storing the results in a temp table or clearing the temp table when the final query is run. Using a single query will likely run quicker and use fewer resources, too. I understand this isn't a basic feature of SQL, but it's not too horribly advanced, either. – computercarguy Jan 03 '20 at 17:44
  • Okay thanks for clarifying. I'll try to apply the answers from the link to my case.. – Nicole Jan 03 '20 at 17:47
  • 1
    Edit question to post query SQL statements. If SELECT query is referenced in DELETE, then the SELECT will be 'run', no need to 'open' the object. – June7 Jan 03 '20 at 17:51
  • Could you post the SQL for your `delete` query, so that we can see how it references the results of your `select` query? – Lee Mac Jan 03 '20 at 22:48

1 Answers1

0

You can achieve it by placing both queries, i.e select query and delete query in a macro,

The macro will be like this open query, under the properties options beneath, pick the the select query open query, under the properties options beneath, pick the the delete query

save the macro.

You can then attach the macro to a command button on a form, then click to run the macro daily or any time you need it.

Sola Oshinowo
  • 519
  • 4
  • 13