Query to get summary results for multiple queries

[ejhatch]

Hi All,

I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.

The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:

QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -


So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.

Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.

The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.

Thanks,

Evan

 

[Moxy1]

Hi Evan,

The solution chap is to use VBA code. Something like this:

Create a RecordSet object based on a querydef object that reads table tblQueryResults where QUERYTORUN = YES

DIM DB1 AS DATABASE
DIM rsQRYSTORUN
DIM qdfQUERYLIST
DIM QRYNAME AS STRING
DIM qdfTHISQUERY
DIM rsTHISQUERYRESULT

SET DB1 = CURRENTDB
SET qdfQUERYLIST = DB1.CREATEQUERYDEF("")

qdfQUERYLIST = "SELECT tblQUERYRESULTS.* FROM tblQUERYRESULTS WHERE (((tblQUERYRESULTS.QUERYTORUN)=YES))"

'Now run the query
SET rsQRYSTORUN = qdfQUERYLIST.OPENRECORDSET()

'Next, check that the recordset contains records
IF rsQRYSTORUN.ABSOLUTPOSITION > -1 THEN

'Now run each query that is returned in the recordset.
DO UNTIL rsQRYSTORUN.EOF
rsQRYSTORUN.MOVEFIRST
WITH rsQRYSTORUN
.EDIT

' You will need to add in the fieldname that holds the name of the query you want to run from your table tblQUERYRESULTS below
QRYNAME = .QUERYNAME
SET qdfTHISQUERY= DB1.QUERYDEFS(QRYNAME)

'Now run the individual query
SET rsTHISQUERYRESULT = qdfTHISQUERY.OPENRECORDSET()

' You can now add any code you like to see if the query has returned any records, count the records, export them excel etc... - eg. IF rsQRYSTORUN.ABSOLUTPOSITION > -1

' Once you have the number of records returned, the following code will update your tblQUERYRESULTS table with appropriate data.
!DateRun = now()
!NumberOfRecords = rsTHISQUERYRESULT.RECORDCOUNT

' This is the code that updates the record in the table
.UPDATE

'Finish the Loop and tidy up
END WITH
LOOP
ELSE
msgbox("No Queries to run")
END IF

set rsTHISQUERYRESULT = NOTHING
set rsQRYSTORUN = NOTHING
set qdfQUERYLIST = NOTHING
set qdfTHISQUERY = NOTHING
set DB1 AS DATABASE


Hope this helps.

Cheers,
Pete