auto populate field in table from 2 previous fields (1 Viewer)

[anthonyphillips]

Hi guys

I have a table for risk assessments and I am wanting to have the probability score and the impact score equal a value in the assessment field.

So for example if the probability is selected as 1 and the impact is selected as 1 the the box will show the word "Green".

If 3 & 3 it will show "Amber"

And 5 & 5 shows "Red" and so on.

5 & 5 is the highest level.

is this possible?

Thanks

Anthony

 

[Atomic Shrimp]

If there is a strict relationship between the scores and the assessments like that, then the assessment ought to be a calculated field in a query, rather than a stored value in a table.

I'm assuming there are other probability/imact combinations you have to deal with besides those where the numbers are the same? - if so, I think you need a lookup table for assessments, something like:

impact, probability, score
1,1,Green
1,2,Green
...
3,3,Amber
...
5,5,Red

(you'd have to populate this table with one row for each valid combination of impact and probability)

Then you can join this assessments lookup table to your assessments table in a query, then show the score field in that query

 

[anthonyphillips]

excellent thank you, however i should have mentioned . . . extreme access noob, so my response is ??? yeah pardon

 

[Atomic Shrimp]

Fair enough...

OK, so create a new table and call it, say, tblScoreLookup - create three columns in it:
'Probability'
'Impact' - define both of these as the same data type as their counterparts in the actual assessments table.
and 'Score' - a text field.

Save the table and (manually) fill it with rows for each possible impact/probability combination
- that should be 25 rows, by my recknoning
On each of those rows, decide what the appropriate score will be for that impact/probability combination and type it into the 'score' field

Now close the table and click on 'query design' to create a new query
It will ask you which tables you want to add - pick the main assessments table, plus the tblScoreLookup table you just created, then close the 'show table' dialog.
Now click on the probability field name in the assessments table and drag it onto the probability field in tblScoreLookup table - it should draw a line between these two, indicating a join
Do the same for impact

Next, double-click any of the fields in the assessments table you would like to see
double-click the 'score' field in tblScoreLookup
(all of these fields should appear in the query design grid)

save the query, then run it - it should show you a grid of data, including the score for each item.

Now, this query will only show you matched records - if someone enters a probability that isn't in the list, or leaves it blank, the whole assessment won't show in the table.

To overcome this, open the query in design mode again, then double-click on one of the lines joining the two tables together - you should be offered a choice - pick the one that looks like 'include all records from the assessments table and only those records from tblScoreLookup where the values are equal'

Do the same for the other join

Both of the joins should now be represented by arrows (as opposed to unadorned lines) - both pointing in the same direction - at tblScoreLookup

Run the query again - all assessments should show, with a blank score where no appropriate score was defined in tblScoreLookup

 

[anthonyphillips]

Thank you for help, i can't believe it was that simple. Your instructions were very easy to follow.

Can i assume that i can use that qry to populate forms and reports ? and will the qry save the output score into the table when the record is saved ?

Thanks

Anthony

 

[Atomic Shrimp]

You can treat a query just like you would treat a table, in most respects (although not all queries produce results that are editable/updatable).

It won't save the scores into the table unless you provide a field in which to store the values and turn your query into an update query (check the Access help on that subject - it's pretty lucid).

But you don't need to store the results - because you can calculate them any time you want them - a form or report based on the query you created above will recalculate the latest results on the fly for you each time the report or form is opened.

Never store what you can calculate is a general rule that leads to sound database design - there are exceptions, like anything else - but if you store something, you'll often have to keep refreshing or recalculating it just so you can rely on it - and if you have to recalculate it like that, there's not much point storing it after all.