I have a database that stores risks, at any one point there can be many risks with each having values from 1 to 5 for current impact (CI) and current probability (CP). Once a week I would like to tally up how many risks have CI of 5, CI of 4 ......... PI of 5, PI of 4 etc. and store them to a new table, so an ongoing high level risk report can be created to show trends.
I am able to do this from within Access using two queries shown below (one feeding into another) and then run the queries manually each week and append the results into a table, to store weekly totals. But I would like to automate the process and wondered how I would go about recreating this is SQL and automating it on the SQL server.
Query 1 (qryFeederOngoingStatusRisks)
Query 2
I then use an append query to append the results of query 2 into a new table that keeps a week by week record of the number of risks at a particular risk level.
I am able to do this from within Access using two queries shown below (one feeding into another) and then run the queries manually each week and append the results into a table, to store weekly totals. But I would like to automate the process and wondered how I would go about recreating this is SQL and automating it on the SQL server.
Query 1 (qryFeederOngoingStatusRisks)
Code:
SELECT Date() AS DateRan,
IIf([R_Impact3]=1,1,0) AS CI1,
IIf([R_Impact3]=2,1,0) AS CI2,
IIf([R_Impact3]=3,1,0) AS CI3,
IIf([R_Impact3]=4,1,0) AS CI4,
IIf([R_Impact3]=5,1,0) AS CI5,
IIf([R_Probability3]=1,1,0) AS CP1,
IIf([R_Probability3]=2,1,0) AS CP2,
IIf([R_Probability3]=3,1,0) AS CP3,
IIf([R_Probability3]=4,1,0) AS CP4,
IIf([R_Probability3]=5,1,0) AS CP5
FROM tblRisks;
Query 2
Code:
SELECT qryFeederOngoingStatusRisks.DateRan,
Sum(qryFeederOngoingStatusRisks.CI1) AS [CI-1],
Sum(qryFeederOngoingStatusRisks.CI2) AS [CI-2],
Sum(qryFeederOngoingStatusRisks.CI3) AS [CI-3],
Sum(qryFeederOngoingStatusRisks.CI4) AS [CI-4],
Sum(qryFeederOngoingStatusRisks.CI5) AS [CI-5],
Sum(qryFeederOngoingStatusRisks.CP1) AS [CP-1],
Sum(qryFeederOngoingStatusRisks.CP2) AS [CP-2],
Sum(qryFeederOngoingStatusRisks.CP3) AS [CP-3],
Sum(qryFeederOngoingStatusRisks.CP4) AS [CP-4],
Sum(qryFeederOngoingStatusRisks.CP5) AS [CP-5]
FROM qryFeederOngoingStatusRisks
GROUP BY qryFeederOngoingStatusRisks.DateRan;
I then use an append query to append the results of query 2 into a new table that keeps a week by week record of the number of risks at a particular risk level.