Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Location
    West Sussex
    Posts
    5

    Unanswered: Adding an extra field to a form

    I have modified the Projects Database template in Access 2007 to provide a risk monitoring database. I have added a table based on two evaluation criteria of impact and probability. Each of these can either be High, Medium or Low. The third field in the table is the result from the matrix e.g. High Impact plus High Probability equals an overall risk assessment of High.

    When I try and add a field onto a form that shows the overall risk assessment based on the probability and impact already enetered, I get the right result but I cannot then add any more inforamtion to the form.

    I have linked both the impact and probability fields of the simple table to the same fields on the original projects table.

    Obviously I would like to show the matrix result on the form and see it changing if I change the impact and/or proability status.

    I think the problem is my lack of understanding of lookup / updating data. Do i need an extra field in a table to add in the result?

    Any help would be greatly appreciated.

    robhope

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Do not use a field to 'store' this information - use and unbound control which references the values of the other fields to display your result.

    Theory is you should *Never (Arguable!) store a value which can always be calculated.

    Certainly in this case at first glance, there is never any need to store it as a field.

    Is the form bound to a query (does it have a query as the recordsource?) If so, you can easily add another control to the query say:

    RiskAssessment: (CalculatedControlBasedOnOtherFieldValues*)

    * You may be able to do this with IIF statements, a function or case statements.

    Please Post a grid of the possible values of each control and the resulting 'Overall Risk' you require and I will assist further.

    Impact Probability
    High High High
    High Med
    High Low
    Med High
    Med Med
    Med Low
    Low High
    Low Med
    Low Low

    Also - what datatype are these fields and what are their names.
    Last edited by garethdart; 03-23-09 at 11:30.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Mar 2009
    Location
    West Sussex
    Posts
    5
    Gareth,

    The form is based upon a table called Risks. I have created a new table called Risk Assessment with the info below:

    Option Probability Impact Overall Risk Assessment
    1 (3) Low (3) Low (3) Low
    2 (3) Low (2) Medium (3) Low
    3 (3) Low (1) High (2) Medium
    4 (2) Medium (3) Low (3) Low
    5 (2) Medium (2) Medium (2) Medium
    6 (2) Medium (1) High (1) High
    7 (1) High (3) Low (2) Medium
    8 (1) High (2) Medium (1) High
    9 (1) High (1) High (1) High

    The plan is to query and report on the overall risk assessment value for all the outstanding risks as well as being able to see the overall risk assessment on the form record. There is a subform with four criteria matrix in a s well so I hope to do the same for this.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Just one last question - Are these Access tables or do you have an SQL backend?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Mar 2009
    Location
    West Sussex
    Posts
    5
    Gareth,

    It is a modified Access 2007 template database. Does this mean there is no SQL?


    robhope

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Ok first maybe use an inline function:

    Add this module:

    Option Compare Database
    Option Explicit

    Public Function strReturnProbability(intImpact As Integer, intProbability As Integer) As String

    strReturnProbability = "Unknown"

    If Not IsNull(intImpact) And Not IsNull(intProbability) Then

    Select Case intImpact

    Case 1

    If intProbability = 3 Then

    strReturnProbability = "Medium"

    ElseIf intProbability = 2 Or intProbability = 1 Then

    strReturnProbability = "High"

    Else
    'Should not get this far

    strReturnProbability = "Unknown"

    End If

    Case 2

    If intProbability = 1 Then

    strReturnProbability = "High"

    ElseIf intProbability = 2 Then

    strReturnProbability = "Medium"

    ElseIf intProbability = 3 Then

    strReturnProbability = "Low"

    Else
    'Should not get here if values are bound and can only be 1, 2 or 3

    strReturnProbability = "Unknown"

    End If

    Case 3
    'It is 3

    If intProbability = 2 Or intProbability = 3 Then

    strReturnProbability = "Low"

    ElseIf intProbability = 1 Then

    strReturnProbability = "Medium"

    Else
    'Should not get here if values are bound and can only be 1, 2 or 3

    strReturnProbability = "Unknown"

    End If

    End Select

    Else

    'One or both of the values are currently null
    'string will remain 'Unknown'

    End If

    End Function

    +++++++++++++++++++++++++++++++++++++++++

    In your query grid, add your table and fields and also add

    Probability: strReturnProbability([intImpact],[IntProbability])

    This uses an inline function and should return your correct results?

    intImpact intProbability Probabiliry
    1 1 High
    1 2 High
    1 3 Medium
    2 1 High
    2 2 Medium
    2 3 Low
    3 1 Medium
    3 2 Low
    3 3 Low
    1 0 Unknown
    0 0 Unknown

    Try that for starters - we made need to add some additional code to update during form events but let me know if you understand the above first.

    The other option is to use a nested IIF statement, although these can be a little complex to debug - I would suggest the above approach unless the query is going to be returning large amounts of data in which case there may be performance issues.

    PS intImpact and intProbability should be replaced with your real fieldnames and I have ASSUMED (naughty me) that they are integers?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Mar 2009
    Location
    West Sussex
    Posts
    5
    Gareth,

    I am afraid that the data is stored as (1) High, (2) Medium or (3) Low in both the Probability and Impact fields - mainly for sorting purposes.

    I have attached a screenshot of the results of the query where the ID field is the unique ID field from the main input form.

    I have never used modules so it is new to me. Would ReturnProbability be OverallRiskAssessment in my case?

    I thought I knew a little about databases but it seems I know even less.

    I am not sure how to proceed.

    robhope
    Attached Thumbnails Attached Thumbnails Risks Query.JPG  

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Thumbs up Exta field on form

    No worries Rob - we can work through this no problem.

    Can you send a screen-shot of your table DESIGN please?

    This will hopefully show the datatype of the fields in question...(I may suggest you change these assuming you don't have any real data in in yet)
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Mar 2009
    Location
    West Sussex
    Posts
    5
    Gareth,

    Please find the screenshot of the table design attached (Risk Table). I added an Overall Risk Assessment field in case the data needed to be stored. The Target Probability and Target Impact are for comparison with the real values.

    I have added an extra field in the Risk assessment table (attached) to store the results numerically. This will allow counting and averaging in reports..

    I have also attached the input from where I would like the Overall Risk Assessment field shown.

    Cheers,

    robhope
    Attached Thumbnails Attached Thumbnails Risk Table.JPG   Risk Assessment Table.JPG   Risk and Task Details form.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •