Translate some codes to a sentence

[Kenneth Ho]

Dear all,
I'm having troubles in doing something like this, hope you may help.

A field stores some text like: 1-2T f 10 days
A code table has two fields, "Code" and "Desc".
In that table, description of "1-2T" is "Take 1 to 2 Tablet(s)",
description of "f" is "for".
The full sentence output will be: Take 1 to 2 Tablet(s) for 10 days.

Is there any way I can translate a text field like this? I really have
no idea. Please also note that "1-2T" & "f" are in the code table while "10
days" is not.

Any idea is VERY MUCH appreciated. Thanks!

Regards,
Kenneth Ho

 

[David F Cox]

This can be done very simply, just as you have set it out. You set up a
lookup table with those two fields, using the code field as the key and
retrieve the translated description using something like:

SELECT desc FROM tbltranslate WHERE code = [mycode];

 

[JK]

Kenneth,

This is a tricky one as it is totally rely on *complete * consistency of
the input, I strongly recommend against this approach. Be it as it may , I
believe that this will do what you are looking for:

+++++++++

Private Function DozeDescription( _
strCode As String) As String
'for Kenneth
'Output: Traslate something like
' "1-2T f 10 days" to
' "Ttake 1-2 tablet for 10 days"

On Error GoTo ErrorHandler

Dim nPos As Long, _
numTabs As Double, _
tmpStr As String, _
tmpDesc As String, _
strUnit As String
'numTabs is double to cater for fractions eg:
'"take 0.5 tablets"

DozeDescription = ""
tmpStr = Trim(strCode)
'tmpStr=1-2T f 10 days"

If Len(tmpStr) = 0 Then 'empty code
'massage if you wish
Exit Function
End If
tmpDesc = "Take "

HowMany:
numTabs = Val(tmpStr) 'numTabs = 1 or 2
If numTabs = 0 Then
'massage if you wish
Exit Function
End If

tmpDesc = tmpDesc & IIf(numTabs = _
Int(numTabs), Int(numTabs), numTabs)
'tmpDesc="take 1" or "Take 0.5 "

nPos = InStr(1, tmpStr, "-")

'looking for the "-"
If nPos = 0 Then 'No range or end of range
tmpStr = Trim(Right(tmpStr, _
Len(tmpStr) _
- Len(Trim(Str(numTabs)))))
'tmpStr="T f 10 days"
GoTo TakeWhat
Else
tmpStr = Trim(Right(tmpStr, _
Len(tmpStr) _
- nPos))
'tmpStr="2T f 10 days"

tmpDesc = tmpDesc & " to "
GoTo HowMany
End If
'strUnit = Left(tmpStr, 1)
tmpStr = Trim(Right(tmpStr, Len(tmpStr) _
- nPos))
'tmpStr="f 10 days"

TakeWhat: ' unita (tablets capsules rtc

Select Case Left(tmpStr, 1)
Case "T"
strUnit = "Tablet"
Case "C"
strUnit = "Capsule"
Case "f" ' No units
'.... etc
Case Else 'unknowon unit
strUnit = ""
End Select

'addind "s" to plural
strUnit = strUnit & IIf(Len(strUnit) > 0 _
And numTabs >= 2, "s", "")

tmpDesc = tmpDesc & " " & strUnit

'checking for "f"
nPos = InStr(1, tmpStr, "f")
If nPos = 0 Then ' no "f" found
GoTo EndExit
Else
tmpStr = Trim(Right(tmpStr, Len(tmpStr) _
- nPos))
'tmpStr="10 days"
tmpDesc = tmpDesc & " for " & tmpStr
End If

EndExit:
DozeDescription = tmpDesc
Exit Function

ErrorHandler:

MsgBox "Error Number: " _
& Err.Number & Chr(13) _
& "Description: " _
& Err.Description

Exit Function

End Function




++++++

Regards/JK

 

[Guest]

On JK's first point it would be much better to devise a coding system where
the user could choose options from lists for different parts of the sentence
using combo boxs to present the lists, because people make errors in
entering even the simplest codes and if the code is not written exactly then
the appropriate sentence text will not be found. Lists also mean the items
don't have to be quite so truncated as they are choosing an item rather than
typing.

 

[JK]

S,

I certainly agree if the code is broken to a number of fields.

however, if you mean to create a translation table to cater for all possible
code, that might be quite a task depending of the possible variation. The
code comprise from at least 4 components (on Kenneth example "1", "-2"
,"T", "F 10 days" where 1 can be from 1 to a max, say 5. "-2" the same +1
(there might not be a range), "T" ??? Tablets, capsules suppositories etc.
say 5 "f 10 days" who knows ? 1-10 days 1 -10 weeks, 1-10 month for
argument sake.

This is 5*6*5*10*10*10=150,000 entries in the Translation table. Do you see
the point?

Beside, that what the Doctor ordered

Regards/JK

 

[David F Cox]

I definitely would not use manual entry for these sorts of code, especially
in mission critical applications, and medicine is one of those.

If you must go down this analysis of the sequence route it needs very
careful validation of possible sequences to avoid ambiguity, probably using
state diagrams.

Be very wary of handwriting of codes. I have seen far too many numbers that
look like other numbers. So far all it has cost is money, (sometimes lots of
it).

I would think about have a safe dosage table (or tables) associated with
such a system as a precaution.

 

[Jamie Collins]

especially
in mission critical applications, and medicine is one of those.

I've worked on a hospital prescribing application (not on an Access/Jet
platform <shudder>): the expression is 'life critical'.

Jamie.

--

 

[JK]

David and Jamie



Looking at the top left corner of my screen I see "Microsoft.Public.Access",
I can't see anything to suggest that I am in medical related Newsgroup



I think that we are digressing from the issue. We have already made clear
the risks associated with the approach as *we* understand it. We have no
idea whatsoever, nor we should , how the codes are entered and what checks
there are, if any, to ensure their correctness.



To talk about life threatening situations is really carrying it to far. We
don't even know how and for what purpose the translations of the codes are
used.



In short, it is not our business!



Kenneth asked a question, each of us replied as he saw fit. It is now up to
Kenneth to accept any or none of the approaches. We are yet to hear from him
(or not).



Regards/JK

 

[Kenneth Ho]

Thanks to all of you!

Actually it's an oracle database. A PowerBuilder program was built on it.
Normally when a dispenser type in some text as mentioned like "1-2T f 10
days", their program can translate it in a full sentence "Take 1 to 2
Tablet(s) for 10 days." and then print out in a label. However, the field
in database only stores "1-2T f 10 days". Now, since I need to use these
data to generate another report additionally, I have to translate again in
Access.
It's true that we have a code table as mentioned:

Code Desc <--- field names
1-2T Take 1 to 2 Tablet(s) <--- record
f for <--- record
and so on.

I agree that we can break the field text into different variables and check
these variables can match in the code table or not. But I don't know how to
do because some of them are in the code table but some of them may be not
( like "10 days" ). So, I'll try JK's procedure.

Any other comments are very much appreciated.

Thanks again.

Regards,
Kenneth Ho

 

[David F Cox]

You are quite right, it is not my business. It is my "calling".

 

[JK]

Kenneth,

1. You would note that my function is based on one string and I see that you
have 2, thus you call the function by joining the 2 two ie [Field1] &
[Field2].
You don't have to worry about spaces because the function trims the
components. If it is "1-2Tf10 days" or it is "1 - 2 T f 10 days" you
will still get the correct result (assuming the function is "boobooles")

2. It is possible to break up the codes but seeing that you need it only for
a report (is that right?) there is no need to break it up, just call the
function in the report. If you need to break up the codes for other purposes
you can use the techniques in the function.

3. to break up "10 days" or "10days" or "10 days"

val("10 days") =10 (numreic)
Replace("10 days","10","")= " days"
trim(" days") ="days"


Regards/JK

 

[Jamie Collins]

David and Jamie
Looking at the top left corner of my screen I see "Microsoft.Public.Access",
I can't see anything to suggest that I am in medical related Newsgroup

I think that we are digressing from the issue. We have already made clear
the risks associated with the approach as *we* understand it. We have no
idea whatsoever, nor we should , how the codes are entered and what checks
there are, if any, to ensure their correctness.

To talk about life threatening situations is really carrying it to far. We
don't even know how and for what purpose the translations of the codes are
used.

In short, it is not our business!

I should have said [OT] but I think it is an important issue. It
*always* genuinely worries me when I see someone apparently at the helm
of a life critical system asking for help in these groups. That's why I
think we (collectively) should always seek to give the best advice e.g.
fix the design flaw rather than workaround with a kludge etc. I'm
always reminded of this war story:

http://groups.google.com/group/comp.databases/msg/9690dda73900ea4f?hl=en&

Jamie.

--

 

[JK]

Jamie,

I fully agree with you if you or I were writing a program and were in
position to point out flaws in the logic or possible risks as we see them.
In that case we would be in a position to get replies in real time and act
(write the program) accordingly .

I *always* look for "what if"s when I write a program, pointing out to
client possible, traps. About half of the times I get "don't worry about it
...." and the other half "Oh, I didn't think about it, yes do it". More so,
even when get "don't worry about it", I always allow for it, transparently,
because in many cases the client will change his/her mind or it becomes an
issue during testing.

However, this is not the case here. Sure we should point out possible
negative consequences when we see them. Having done that, it is up to the
person who ask the question to evaluate the relevance of our recommendation
to his/her particular requirements. The fact that this is a medical issue
makes no difference. I bet you that there have been many question here that
were related to medical applications and we did not know about it. Are we
going to ask every "askee" for the type of application his/her question
relates to before we answer the question? of course not.

The examples in your link are illustrations of bad management and lack of
effective testing, none which is of our concern here.

However, may have to agree to disagree and leave it at that.

Regards
Jacob

David and Jamie
Looking at the top left corner of my screen I see
"Microsoft.Public.Access",
I can't see anything to suggest that I am in medical related Newsgroup


I think that we are digressing from the issue. We have already made clear
the risks associated with the approach as *we* understand it. We have no
idea whatsoever, nor we should , how the codes are entered and what
checks
there are, if any, to ensure their correctness.

To talk about life threatening situations is really carrying it to far.
We
don't even know how and for what purpose the translations of the codes
are
used.

In short, it is not our business!

I should have said [OT] but I think it is an important issue. It
*always* genuinely worries me when I see someone apparently at the helm
of a life critical system asking for help in these groups. That's why I
think we (collectively) should always seek to give the best advice e.g.
fix the design flaw rather than workaround with a kludge etc. I'm
always reminded of this war story:

http://groups.google.com/group/comp.databases/msg/9690dda73900ea4f?hl=en&

Jamie.

 

[Jamie Collins]

I bet you that there have been many question here that
were related to medical applications and we did not know about it. Are we
going to ask every "askee" for the type of application his/her question
relates to before we answer the question?

A database designer posts details of their table called MoneyLaundering
which includes a 1NF violation. Do you tell them to fix the flaw?

Jamie.

--

 

[David F Cox]

No, I ask them to post it to me.

A database designer posts details of their table called MoneyLaundering
which includes a 1NF violation. Do you tell them to fix the flaw?

Jamie.