Access SQL: Run multiple select and delete sub queries whiles recording counts in a single query

Question

I have a number of select and delete queries that I have to run in order.

The select query returns a count of rows and the delete query deletes them. Then I run another select and another delete etc.

I would like to be able to bundle it all up into a big uber query so I can run it all in one go. They need to be run in order because the records returned in query 2 will be affected by those deleted in query 1.

I would like to finish up with the results output in a table containing fields for count of the select query's along with the records deleted as follows:

Query1 Count | Delete1 Count | Query2 Count | Delete2 Count| etc.....

I currently have all select and delete queries working separately. Any help would be greatly appreciated.

Answer 1

I have a number of select and delete queries that I have to run in order.

The select query returns a count of rows and the delete query deletes them. Then I run another select and another delete etc.

I would like to be able to bundle it all up into a big uber query so I can run it all in one go. They need to be run in order because the records returned in query 2 will be affected by those deleted in query 1.

I would like to finish up with the results output in a table containing fields for count of the select query's along with the records deleted as follows:

Query1 Count | Delete1 Count | Query2 Count | Delete2 Count| etc.....

I currently have all select and delete queries working separately. Any help would be greatly appreciated.

Are you saying that you run the select query just to get the count of the rows that will be deleted by the delete query?  I don't see any reason why you would do that, when you can get the count of records affected by the delete query after its execution, without running the select query.

If we are talking about queries run against an Access (JET/ACE) database, as opposed to a SQL Server database, or some other server database?  SQL Server queries can contain multiple SQL statements, allowing you to do what you want, but Access doesn't permit that.  What you can do, though, is use a VBA procedure to run a series of queries and get the results of each.  For example:

'------ start of example code ------

Sub DeleteStuff

    With CurrentDb()

        .Execute "DeleteQuery1", dbFailOnError

        .Execute "INSERT INTO tblResults (QueryName, ResultCount) Values( 'DeleteQuery1', " & .RecordsAffected & ")", dbFailOnError

        .Execute "DeleteQuery2", dbFailOnError

        .Execute "INSERT INTO tblResults (QueryName, ResultCount) Values( 'DeleteQuery2', " & .RecordsAffected & ")", dbFailOnError

       .Execute "DeleteQuery3", dbFailOnError

       .Execute "INSERT INTO tblResults (QueryName, ResultCount) Values( 'DeleteQuery3', " & .RecordsAffected & ")", dbFailOnError

    End With

End Sub

'------ end of example code ------

That code will -- or ought to; it's air code, after all -- execute each delete query and store the count of deleted records as a new record in a table called "tblResults".