Database design question, linking numbers to predetermined text

[Matt T&J]

Hello,



I am fairly new to access and have been trying to build a database that will produce reports with automatic comments based on values set for certain fields.

For example I work in a soil analysis company and one of the fields is SoilPH. this will usually range from 4-8 and depending on what the value is I would like the report to comment on the result and give an additional comment to recommend how to correct a value that is too low or high.

There are going to be around 17 fields with a numerical value like this needing the generation of predefined comments based on the number that is entered.

Currently all comments are input into a template word document but the process is very time consuming and i'm hoping that access can at least semi-automate it .


At the moment I have a table called SoilSample with fields SampleID, SampleName, pHValue, pHcomment, pHaction.

pHcomment and pHaction are both long text data type.

I then have another table called pHcomments. This has fields pH (this is the number i receive from our analysis of a soil); CommentNumber (number); Comment (long text); Action (long text).

e.g. pH =2, CommentNumber =1, Comment =Acid Soil, Action =No liming required

So each pH value has an assigned comment about the value and then an action to correct the value.

There are separate tables for the other 17 values and their comments just like this one.

This is all put together in a form SoilSample with the fields SampleID, SampleName, pHValue. In addition it has 3 text boxes (DLookUps) 1,2 and 3

1 =DLookUp("[CommentNumber]","[phComments]","[PH]>=[pHValue]")
2 =DLookUp("[Comment]","[phComments]","[CommentNumber]=[pHNumberLookup]")
3 =DLookUp("[Action]","[phComments]","[CommentNumber]=[pHNumberLookup]")

this brings in all the data onto the form when I enter the pHValue i receive from analysis.

The problem is I would like these comments to be stored in a field in the SoilSample table and also I would like to be able to edit them (in some cases different comments apply than normal for a given pH value).

Is this possible? Is there a better way of doing this?

Any input would be much appreciated,

Thank you,

Matt

[June7]

Options:

1. Store the comment ID

2. Store the comment itself

Both will require VBA to 'automate'.

Are those DLookup expressions returning correct value? I would expect an issue because variable parameter is within quote marks. Variables should be concatenated.

DLookUp("[CommentNumber]","[phComments]","[PH]>=" & [pHValue])