How do I repeat a query 'n' times? (1 Viewer)

[Franky G]

Hi all,

I've got a query which calculates the total applications received between two dates, and the value of these applications. At the moment, I'm running the query 13 times-as an update table query which gives me the totals for a year.

How can I make the query run, append to the table, and then run repeatedly each time prompting me to input the period start and end dates? It's really just the 'repeat' part I need, as I know how to get Access to prompt me for the dates.

Just to round out the picture, I plan to build tables for the past couple of years, and then use graphs to compare year on year. I haven't really figured how to do this yet, but thinking that keeping the Count of warrants and Value of work graphs separate would be best? Any pointers gratefully accepted!

Thanks

FrankyG

 

[simongallop]

If this is a one off then I would have thought double clicking on the append query to run it and then type in your dates would be the easiest. If this is something that will be happening on a regular basis then I would suggest that you create a table called "My_Dates"with 2 columns being Start_Date and End_Date. Type in the periods here and then create a module which does the following:

Opens a recordset of the table, selects the dates from the first row, paste the dates into the criteria of the query, runs the query, moves down one row in the table and repeats the procedure.

Sub Rep_Query()
Dim MyRS as recordset
Dim dteStart as date
Dim dteEnd as date
Dim strSQL as string
Dim qdfApp as QueryDef

DoCmd.SetWarnings False

Set MyRS = CurrentDB.OpenRecordset("My_Dates",dbOpenDynaset)
Set qdfApp = Currentdb.QueryDefs("NameOfQuery")

MyRS.MoveFirst
dteStart = MyRS("Start_Date")
dteEnd = MyRS("End_Date")

Do
strSQL = "SELECT ..........Where datefieldname = #" & dteStart & "# AND EndDateFieldName = #" & dteEnd & "# ....."
qdfApp.SQL = strSQL
DoCmd.OpenQuery "queryname"

MyRS.movenext
Loop until MyRS.EOF = True
MyRS.Close
qdfApp.Close
DoCmd.SetWarnings True

End Sub

For the SQL bit you will need to open the query in SQL view and copy the SQL code into the string. Make sure that the lines look like:

"My string starts here and for line breaks " & _
" I make sure that I add the ampersand and underline " & _
" end of string;"

HTH

 

[Rich]

I think you're making this far more complex than needed. A totals query grouped by year will return the records. Is your data normalised? separate tables for each year isn't recommended and a crosstab will enable you to produce graphs year on year