Database questions, giving text a numerical value

[Nikky]

Hello,
I am trying to create a database for a hospital to keep track of diabetic
and dialysis patients and their risk of foot ulceration. To make it easy for
the nurses to follow, we want to use text in the database that will represent
numbers so that an overall risk score can be calculated. We would like for
high=3, mod=2, and low=1 in the Vascular, Skin, mobility and Structure
columns. In the HbA1c column we would like for low and high=1 and stable=0.
In the diabetic column we would like for "n"=0 and "y"=1.

Here is an example of the layout:

Name Education Vascular Skin Diabetic Mobility HbA1c
Structure
a 7/8/2009 mod mod y mod stable mod
b 7/9/2009 high mod y low low low
c 7/10/2009 low low y high high high
d 7/11/2009 mod high n high stable low
e 7/12/2009 high mod n high stable low
f 7/13/2009 low low n high stable low
g 7/14/2009 low high y high low high
h 7/15/2009 low high y low high high
i 7/16/2009 high low n low stable high
j 7/17/2009 low high y low low mod
k 7/18/2009 mod high n low high high
l 7/19/2009 high high n low high high


Also, do you have any ideas about how to make a flag show up or some kind of
background change when the patients have not received education for 2 months
(under the Education column)?

I'm sorry if this is confusing!! Thanks.

 

[jamescox]

I copied your table of info into a worksheet starting at cell A4, so
that gives you some idea of how to adjust the cells in the formulas.

For High Med Low => 3,2,1 conversions use the following:

=IF(UPPER(D4)="HIGH",3,IF(UPPER(D4)="MOD",2,IF(UPPER(D4)="LOW",1,"")))

For Low High Stable => 1,1,0 conversions use:

=IF(OR(UPPER(G4)="LOW",UPPER(G4)="HIGH"),1,IF(UPPER(G4)="STABLE",0,""))

For N Y => 0,1 conversions use:

=IF(UPPER(E4)="N",0,IF(UPPER(E4)="Y",1,""))

For highlighting cells if the training is more than two months in the
past, use the following formula in a Conditional Formula:

=EDATE(B4,2) < TODAY()

Some comments:

1. These formulas could be placed in a group of cells to the right of
data entry area (Columns A through H)

2. The conversion formulas are a bit more complex than they might have
to be, but use UPPER to make sure 'mod', 'Mod', 'MoD', 'MOD' and all
such variations are recognized.

3. You could use Data Validation to put a drop-down arrow in each data
entry cell which would allow the nurses to just select an entry and not
require them to type anything.

Hope this helps...

 

[Nikky]

Thank you very much! It was extremely helpful.