How to tally individual values from specific fields. (1 Viewer)

[Zedster]

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)

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

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.

 

[MajP]

I do not think there is any reason to do any of that. You should easily be able to run an aggregate query that sums the weekly number of risks at a given level without any temp tables, or complex iif. Can you show a table diagram?

 

[Zedster]

The two smallint fields contain values from 1 to 5 for each R_ID

each week I want to record to a seperate table the quantity of risks at 5,4,3,2,1 etc.

 

[MajP]

Why a separate table, when a simple query would work?

 

[Zedster]

Because I need to keep an ongoing record on a week by week basis of how many risks are at level 5 how many at level 4. Each week I am expected to produce charts for management showing whether the trend over time i.e are high level risks increasing or decreasing. A simple query can only show a snapshot in time not record a trend and I would rather not record it manually as it is error prone and needs me to be around every Friday to capture the data.

It is worth adding the impact and probability for any given risk changes as mitigation takes place. In a ideal world there would be 10 risks at level 5 week 1, they change to 4 week 2, 3 week 3, 2 week 4 etc. What I need to do is show in a chart that such progress is or is not occurring. To further complicate matters at week 1 there may be 10 at week 2 there could be 14, week 3 there could be 6 if all other risks have been closed.

 

[ebs17]

Your queries are Jet-SQL.

SELECT Date() AS DateRan,

' replace with
SELECT Year(R_DateRaised) * 100 + DatePart("ww", R_DateRaised) AS KW,

In the second query you then group according to the week and thus have weekly evaluations of all data. With a period filter ...

WHERE R_DateRaised BETWEEN [A] AND [B]

you can limit to a desired area.

I would go about recreating this is SQL and automating it on the SQL server

Then of course you write it in T-SQL.

 

[Zedster]

thanks

 

[Pat Hartman]

Because I need to keep an ongoing record on a week by week basis of how many risks are at level 5 how many at level 4. Each week I am expected to produce charts for management showing whether the trend over time i.e are high level risks increasing or decreasing. A simple query can only show a snapshot in time not record a trend and I would rather not record it manually as it is error prone and needs me to be around every Friday to capture the data.

It is worth adding the impact and probability for any given risk changes as mitigation takes place. In a ideal world there would be 10 risks at level 5 week 1, they change to 4 week 2, 3 week 3, 2 week 4 etc. What I need to do is show in a chart that such progress is or is not occurring. To further complicate matters at week 1 there may be 10 at week 2 there could be 14, week 3 there could be 6 if all other risks have been closed.

As Maj was trying to tell you, you don't need a separate table. You use a query for your report or chart. The query takes a parameter that tells it the data range. It might be simpler to use a crosstab query so you don't have to hard code the variables but you still have to group by week first.

 

[Zedster]

I guess I am still not getting it.

Example at week 1 I have 3 risks with the following impact and probability values stored in the database:

Risk 1 > I=5 P=5
Risk 2 > I=5 P=5
Risk 3 > I=5 P=5

The user runs the query and it comes back that there are 3 risks with I=5 and 3 risks are at P=5, all good

On week two mitigation actions are taken on all these risks and as a consequence the probability of all three reduces to 4. The database is changed to reflect this for all three risks and now shows:

Risk 1 > I=5 P=4
Risk 2 > I=5 P=4
Risk 3 > I=5 P=4

The user runs the query and it comes back that there are 3 risks with I=5 and 3 risks are at P=4.

How do I create a trend graph that reflects that the risk probability has reduced between weeks one and two because the query can only ever reflect the probability on the week the query was run, it has no knowledge of what the probability value was in the preceding weeks. The reason I am storing the values each week is so the historic trends of all risks can be assessed over time.

 

[ebs17]

You have such a nice clear table with dates. In Jet, for a daily evaluation, I would create a crosstab query per risk type and JOIN them. In addition, you can use the parameters to limit the display to periods of interest.

For a weekly evaluation, R_DateRaised would be converted to the Monday of the week, for example, a year/month format would be used for monthly evaluations, etc.

PARAMETERS parStart Date, parEnd Date;
SELECT
   I.R_DateRaised,
   I.[CI-1],
   I.[CI-2],
   I.[CI-3],
   I.[CI-4],
   I.[CI-5],
   P.[CP-1],
   P.[CP-2],
   P.[CP-3],
   P.[CP-4],
   P.[CP-5]
FROM

(TRANSFORM
   COUNT(R_Impact3) AS CI
SELECT
   R_DateRaised
FROM
   tblRisks
WHERE
   R_DateRaised BETWEEN parStart AND parEnd
GROUP BY
   R_DateRaised
PIVOT
   "CI-" & R_Impact3) AS I

INNER JOIN
        
(TRANSFORM
   COUNT(R_Probability3) AS PI
SELECT
   R_DateRaised
FROM
   tblRisks
WHERE
   R_DateRaised BETWEEN parStart AND parEnd
GROUP BY
   R_DateRaised
PIVOT
   "CP-" & R_Probability3) AS P
 
ON I.R_DateRaised = P.R_DateRaised

 

[MajP]

How do I create a trend graph that reflects that the risk probability has reduced between weeks one and two because the query can only ever reflect the probability on the week the query was run, it has no knowledge of what the probability value was in the preceding weeks. The reason I am storing the values each week is so the historic trends of all risks can be assessed over time.

Are you using a commercial application for risk management and having to tap into the backend? Because no application I have used for RM or any home grown app is designed as you state, and doing trend analysis is easy.

Most are logging status by date so I have never seen a requirement to do what you are asking.

Normally Something like this

tblRisk
-RiskID
-ProjectID_FK 'foreign key to associate to some project
-RiskType ' Cost, Schedule, Performance
-RiskCreationDate '
-Status 'Open or Close
(Optional P and L fields or just stored in the Log below)
-InitialProbability
-InitialLikelihood

tblUpdateLog
-UpdateID
-RiskID_FK
-DateUpdate
-RiskProbability 1 to 5
-RiskLikelihood 1 to 5

tblMitigation
-MitigationID
-RiskID_FK
-MitigationName
-MitigationDescription
-MitigationPercentComplete
-MitigationPlannedStartDate
-MitigationActualStartDate
-MitigationPlannedCompleteDate
-MitigationActualCompleteDate

tblMitigationSteps
-MitigationStepID
-MitigationID_FK
-MitigationStepName
-MitigationStepDescription
-MitigationStepPlannedStartDate
-MitigationStepActualStartDate
-MitigationStepPlannedCompleteDate
-MitigationStepActualCompleteDate

In this way you log the current status over time and can have a history of the mitigation and risk burn down. Then showing risk burn down graphically over time is trivial. There may be some modification to the above where mitigation steps details does not exist

If you are telling me you are not logging the updates and change the P and L (probability and likelihood) without a record logged, then this is one poorly designed risk management software or home built application.

Interface wise. The P and L are on the main risk creation form but log the initial P and L into the update log not the main risk table.
Normally when a mitigation is 100% complete it forces you to create an Update and log a new P and L,