Too many queries, trying to code multiple update tasks

[bhoran]

I have a database with 1 update query for each department i.e. department 1 - 160.

The update query updates a table (simply storing the deptno) that is then used to make another table and print a report.

What I want to do is use code to loop through that process, ie for each record on department table run the make table query then print the report and then move onto the next record.

The departments is broken into different reporting funcions e.g. balance sheet, profit and loss, Operations, etc etc.

I know conceptually what I want but putting it practice is a little tougher as my programming skills are limited (but getting better)

I am thinking something like

Do:
make table query
print report

until:
deptno = top where [Repfunct] = 'balancesheet'

So I am wanting to make the table run the report until I reach the last deptno for that reporting function.

I started to try to write some code but I really and quite confused as to how I go about it.

my make table query is fairly complicated (and will change depending on the reporting function) so running the query maybe easier than adding the code??:
SELECT DISTINCTROW First(GLPlans.[PRFT CNTRE]) AS [FirstOfPRFT CNTRE], Last(GLPlans.[PRFT CNTRE]) AS [LastOfPRFT CNTRE], First(Department.DEPARTMENT) AS FirstOfDEPARTMENT, Last(Department.DEPARTMENT) AS LastOfDEPARTMENT, First(Department.MANAGER) AS FirstOfMANAGER, GLPlans.[ACC NO], GLAccounts.[ACC DESC], GLAccounts.[ACC TYPE], Sum([GLPLANS]![JUN5]) AS MthAct, Sum([GLPLANS]![JUN2]) AS MthBud, ([MthBud]-[MthAct]) AS VAR1, +IIf([MthBud]=0,0,(([MthAct]/[MthBud])-1)) AS [%1], Sum([GLPLANS]![JUN4]) AS MthLF, ([MthLF]-[MthAct]) AS VAR2, +IIf([MthAct]=0,0,(([MthLF]/[MthAct])-1)) AS [%2], Sum([GLPLANS]![JUL5]+[GLPLANS]![AUG5]+[GLPLANS]![SEP5]+[GLPLANS]![OCT5]+[GLPLANS]![NOV5]+[GLPLANS]![DEC5]+[GLPLANS]![JAN5]+[GLPLANS]![FEB5]+[GLPLANS]![MAR5]+[GLPLANS]![APR5]+[GLPLANS]![MAY5]+[GLPLANS]![JUN5]) AS ACTYTD, Sum([GLPlans]![JUL2]+[GLPlans]![AUG2]+[GLPlans]![SEP2]+[GLPlans]![OCT2]+[GLPlans]![NOV2]+[GLPlans]![DEC2]+[GLPlans]![JAN2]+[GLPlans]![FEB2]+[GLPlans]![MAR2]+[GLPlans]![APR2]+[GLPlans]![MAY2]+[GLPlans]![JUN2]) AS BUDYTD, ([BUDYTD]-[ACTYTD]) AS VAR3, +IIf([ACTYTD]=0,0,(([BUDYTD]/[ACTYTD])-1)) AS [%3], Sum([GLPlans]![JUL4]+[GLPlans]![AUG4]+[GLPlans]![SEP4]+[GLPlans]![OCT4]+[GLPlans]![NOV4]+[GLPlans]![DEC4]+[GLPlans]![JAN4]+[GLPlans]![FEB4]+[GLPlans]![MAR4]+[GLPlans]![APR4]+[GLPlans]![MAY4]+[GLPlans]![JUN4]) AS REESTYTD, [REESTYTD]-[ACTYTD] AS VAR4, +IIf([ACTYTD]=0,0,(([REESTYTD]/[ACTYTD])-1)) AS [%4], Sum([GLPlans]![JUL2]+[GLPlans]![AUG2]+[GLPlans]![SEP2]+[GLPlans]![OCT2]+[GLPlans]![NOV2]+[GLPlans]![DEC2]+[GLPlans]![JAN2]+[GLPlans]![FEB2]+[GLPlans]![MAR2]+[GLPlans]![APR2]+[GLPlans]![MAY2]+[GLPlans]![JUN2]) AS BUD, Sum([GLPlans]![JUL4]+[GLPlans]![AUG4]+[GLPlans]![SEP4]+[GLPlans]![OCT4]+[GLPlans]![NOV4]+[GLPlans]![DEC4]+[GLPlans]![JAN4]+[GLPlans]![FEB4]+[GLPlans]![MAR4]+[GLPlans]![APR4]+[GLPlans]![MAY4]+[GLPlans]![JUN4]) AS REEST INTO PCentre
FROM GLAccounts RIGHT JOIN ((Department LEFT JOIN cc ON Department.[PRFT CNTRE] = cc.[Cost Centre]) RIGHT JOIN GLPlans ON Department.[PRFT CNTRE] = GLPlans.[PRFT CNTRE]) ON GLAccounts.[ACC NO] = GLPlans.[ACC NO]
WHERE (((Department.[PRFT CNTRE])=[CC]![Cost Centre]))
GROUP BY GLPlans.[ACC NO], GLAccounts.[ACC DESC], GLAccounts.[ACC TYPE]
HAVING (((Sum([GLPLANS]![JUN5]))<>0)) OR (((Sum([GLPLANS]![JUN2]))<>0)) OR (((Sum([GLPLANS]![JUN4]))<>0)) OR (((Sum([GLPLANS]![JUL5]+[GLPLANS]![AUG5]+[GLPLANS]![SEP5]+[GLPLANS]![OCT5]+[GLPLANS]![NOV5]+[GLPLANS]![DEC5]+[GLPLANS]![JAN5]+[GLPLANS]![FEB5]+[GLPLANS]![MAR5]+[GLPLANS]![APR5]+[GLPLANS]![MAY5]+[GLPLANS]![JUN5]))<>0)) OR (((Sum([GLPlans]![JUL2]+[GLPlans]![AUG2]+[GLPlans]![SEP2]+[GLPlans]![OCT2]+[GLPlans]![NOV2]+[GLPlans]![DEC2]+[GLPlans]![JAN2]+[GLPlans]![FEB2]+[GLPlans]![MAR2]+[GLPlans]![APR2]+[GLPlans]![MAY2]+[GLPlans]![JUN2]))<>0)) OR (((Sum([GLPlans]![JUL4]+[GLPlans]![AUG4]+[GLPlans]![SEP4]+[GLPlans]![OCT4]+[GLPlans]![NOV4]+[GLPlans]![DEC4]+[GLPlans]![JAN4]+[GLPlans]![FEB4]+[GLPlans]![MAR4]+[GLPlans]![APR4]+[GLPlans]![MAY4]+[GLPlans]![JUN4]))<>0)) OR (((Sum([GLPlans]![JUL2]+[GLPlans]![AUG2]+[GLPlans]![SEP2]+[GLPlans]![OCT2]+[GLPlans]![NOV2]+[GLPlans]![DEC2]+[GLPlans]![JAN2]+[GLPlans]![FEB2]+[GLPlans]![MAR2]+[GLPlans]![APR2]+[GLPlans]![MAY2]+[GLPlans]![JUN2]))<>0)) OR (((Sum([GLPlans]![JUL4]+[GLPlans]![AUG4]+[GLPlans]![SEP4]+[GLPlans]![OCT4]+[GLPlans]![NOV4]+[GLPlans]![DEC4]+[GLPlans]![JAN4]+[GLPlans]![FEB4]+[GLPlans]![MAR4]+[GLPlans]![APR4]+[GLPlans]![MAY4]+[GLPlans]![JUN4]))<>0))
ORDER BY First(GLPlans.[PRFT CNTRE]), Last(GLPlans.[PRFT CNTRE]), GLPlans.[ACC NO];

Any help much appreciated.

 

[bhoran]

I think I made my question overly complicated!

I need a process to run queries and print a report for each department.

I have the following code:
Private Sub Command0_Click()
Dim strSQL As String
Dim strOldSQL As String


DoCmd.SetWarnings False
strSQL = &quot;UPDATE DISTINCTROW cc SET cc.[Cost Centre] = '32121'&quot;
'change the sql property of the query &quot;qselMyQuery&quot;
strOldSQL = ChangeSQL(&quot;010&quot;, strSQL)
'your query will now have a new sql value

DoCmd.OpenQuery &quot;010&quot;, acViewNormal, acEdit
DoCmd.OpenQuery &quot;BudControlTestcreate&quot;, acViewNormal, acEdit
DoCmd.OpenReport &quot;BudCntrlTTEST&quot;, acPreview
DoCmd.SetWarnings True

End Sub

Which changes the SQL on the update record and then runs the queries and the report.

I need to make the update value of the query a variable based on the department table and I need this to step through each department on the table for a particular set of departments e.g. run the above code for each departmentno where the reportfunction = 'balancesheet'

when we are at departmentno = 01 the update will be '01' then step to departmentno = 02 and the update will be '02'

so on and so forth until there are no more departments where the reporting function = 'balancesheet'.

Thanks in advance.

 

[bhoran]

I have it sorted I decided to go with this please let me know if you think it can be improved.

Private Sub Command0_Click()
Dim rsPrftCntreNo As Recordset
Dim strSQL As String
Dim strOldSQL As String
Dim txtDeptNo As String

DoCmd.SetWarnings False

Set rsPrftCntreNo = CurrentDb.OpenRecordset(&quot;select [PRFT CNTRE] FROM DEPARTMENT WHERE [REPORTFUNCT]='BalanceSheet';&quot
rsPrftCntreNo.MoveFirst

Do Until rsPrftCntreNo.EOF
txtDeptNo = rsPrftCntreNo![PRFT CNTRE]
Debug.Print txtDeptNo

strSQL = &quot;UPDATE DISTINCTROW cc SET cc.[Cost Centre]='&quot; & txtDeptNo & &quot;'&quot;
'change the sql property of the query &quot;qselMyQuery&quot;
strOldSQL = ChangeSQL(&quot;010&quot;, strSQL)
'your query will now have a new sql value

DoCmd.OpenQuery &quot;010&quot;, acViewNormal, acEdit
DoCmd.OpenQuery &quot;BudControlTestcreate&quot;, acViewNormal, acEdit
DoCmd.OpenReport &quot;BudCntrlTTEST&quot;, acPrint
rsPrftCntreNo.MoveNext

Debug.Print strSQL
Loop
DoCmd.SetWarnings True
End Sub