new table field

[lnkranio]

I am a new user of a database created by someone else. I have some fields
that are filled out erratically by the users who input data. I would like to
create some new table fields that aggregate these answers into something
analyzable. For example, a separate new field that takes "yes, often,
sometimes, usually, no, never" and converts these to "yes" and "no". Or
takes a list of related symptoms and groups them by my definitions, or takes
3 different common misspellings and defines them as one disease, or outputs
gestational periods into premature vs full term, etc. How do I make a new
field that fills itself in based on the value in another field?

New database updates are downloaded every few weeks so I'd like to not have
to redo this each time.

Thanks much,
Linda

 

[John W. Vinson]

I am a new user of a database created by someone else. I have some fields
that are filled out erratically by the users who input data. I would like to
create some new table fields that aggregate these answers into something
analyzable. For example, a separate new field that takes "yes, often,
sometimes, usually, no, never" and converts these to "yes" and "no". Or
takes a list of related symptoms and groups them by my definitions, or takes
3 different common misspellings and defines them as one disease, or outputs
gestational periods into premature vs full term, etc. How do I make a new
field that fills itself in based on the value in another field?

New database updates are downloaded every few weeks so I'd like to not have
to redo this each time.

Thanks much,
Linda

You do not need to - in fact *shouldn't* - put this calculated field in any
Table. Instead, use a Query to dynamically calculate the value. You may need
to download the updates into a table in a database (.mdb file? SQL/Server
instance? something else?) that you manage, and have Queries (perhaps with
auxilary tables) to do this translation.

For instance you could have a little two field table with values

"Yes"; Yes
"Often"; Yes
"Sometimes"; Yes
"Usually"; Yes
"No"; No
"Never"; No

You could join this table to your multivalue table by the text field and give
you the yes/no field.

Similarly, you could have a translation table with values like

"High blood pressure"; "Hypertension"
"Hyptertension"; "Hypertension"
"HBP"; "Hypertension"
<and so on>

If your source database has no control over the vocabulary, though, you're
going to have no end of trouble with grey areas and misspellings (you can get
off the home keys and Hypertension will come out Ju[ertemsopm for example!)
Is there any way to talk to the source and have some professionalism added to
the data entry?

 

[Jack Cannon]

You do not need a new field.
Just create a combo or list box with the items listed as:

yes
often
sometimes
usually
no
never

The hidden column would be either Yes or No and it would be bound to the
Yes/No field in the appropriate record.

Jack Cannon

 

[John Spencer]

The solution is dependent on what you mean by "New database updates are
downloaded every few weeks". Does that mean just data is downloaded or
the entire application is downloaded?

In either case, John Vinson's suggestion of creating conversion tables
is your best bet. An additional database with your conversion tables in
it and links to the tables in the downloaded data will be the best
solution. You can then build queries to get the "fixed" data from your
conversion tables by joining the conversion tables to the downloaded
tables in queries.

Post back if you need help with setting this up.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[lnkranio]

Thanks - it sounds like the conversion tables are what I'm looking for. But
I'm afraid I still don't understand how to make a table that will create a
new field based on elements in another field. I can't seem to figure out how
to make a new table that references the correct field in an existing table,
or how to take a long list of entries and output a shorter (correct) list.

For example: I have records that in field 1 can be A1, A2, B, C1, or C2,
and in field 2 are empty, 0, 1, or 2. I need to evaluate the As, Bs, and Cs
with respect to several other variables; empty or 0 in field 2 are of
interest, but 1 and 2 are not.

I probably have dozens of these conversions to draw up. The dataset is a
mess.

Thanks for your help,
Linda

 

[lnkranio]

Sadly, the data quality can't be helped - input is by patients. I may be a
newbie but I certainly would not have designed a questionnaire that gave the
patient this much latitude. But this data took years to accumulate and it's
too valuable to lose. And the only person who knows this project in detail
is long gone. The actual database management/downloads is being done by an
overworked guy in a distant IT department who I haven't had a chance to meet
yet. I'm a bit over my head here.

Linda

I am a new user of a database created by someone else. I have some fields
that are filled out erratically by the users who input data. I would like to
create some new table fields that aggregate these answers into something
analyzable. For example, a separate new field that takes "yes, often,
sometimes, usually, no, never" and converts these to "yes" and "no". Or
takes a list of related symptoms and groups them by my definitions, or takes
3 different common misspellings and defines them as one disease, or outputs
gestational periods into premature vs full term, etc. How do I make a new
field that fills itself in based on the value in another field?

New database updates are downloaded every few weeks so I'd like to not have
to redo this each time.

Thanks much,
Linda

You do not need to - in fact *shouldn't* - put this calculated field in any
Table. Instead, use a Query to dynamically calculate the value. You may need
to download the updates into a table in a database (.mdb file? SQL/Server
instance? something else?) that you manage, and have Queries (perhaps with
auxilary tables) to do this translation.

For instance you could have a little two field table with values

"Yes"; Yes
"Often"; Yes
"Sometimes"; Yes
"Usually"; Yes
"No"; No
"Never"; No

You could join this table to your multivalue table by the text field and give
you the yes/no field.

Similarly, you could have a translation table with values like

"High blood pressure"; "Hypertension"
"Hyptertension"; "Hypertension"
"HBP"; "Hypertension"
<and so on>

If your source database has no control over the vocabulary, though, you're
going to have no end of trouble with grey areas and misspellings (you can get
off the home keys and Hypertension will come out Ju[ertemsopm for example!)
Is there any way to talk to the source and have some professionalism added to
the data entry?

 

[John Spencer]

A conversion table would consist of two fields
SourceValue (unique values to be converted - no repeated values)
CorrectValue (value you want to use - you can repeat values in this
field if more than one source value matches the same correct value)

so you might have records like (Source : Correct)
A1 : 0
A2 : 0
B : 1
B1 : 1
Bx : 1
C1 : 2
C2 : 2

Then you link the Source table field that contains the source values to
this conversion table sourceValue field.

-- Create a query that has the source table and the Conversion table as
sources.
--Drag from the source table field to the Conversion table field to set
up the join.
-- double-click on the join line and in the dialog select option 2 or 3
whichever shows all records from the source table and only matches from
the conversion table.
-- Add the fields from the source table you want to see and the
CorrectValue from the conversion table in place of the associated source
field.

You can have multiple conversion tables or you can reuse the same
conversion table by adding it multiple times for different fields. If
you use a conversion table for multiple fields, then the Source value
field's value must always have the same conversion for the differnt
source table fields.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[lnkranio]

That did the trick - thanks! The data is looking better already.

Linda

 

[lnkranio]

I have one more problem - I can't seem to figure out how to get the
conversion tables to handle blank fields. I need to be able to tell the
database that a blank entry for a specific field is the same as "0", "no", or
"no diagnosis". But I can't seem to combine a blank with anything else.

Linda

 

[John Spencer]

Ok, Nulls are hard to handle in this situation.

You could use something like the following. One problem is that this
will change ALL values that cannot be matched to NS. So if you had a
value in CS_Patient_History.Syndromic that was not listed in the syndCT2
table you would see CS for that as well as for null values.

SELECT CS_Patient_History.Syndromic
, Nz(syndCT2.Field2,"NS") as Field2
FROM CS_Patient_History LEFT JOIN syndCT2 ON
CS_Patient_History.Syndromic = syndCT2.Field1;

The NZ function replaces null values with whatever is after the comma.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[James A. Fortune]

I am a new user of a database created by someone else. I have some fields
that are filled out erratically by the users who input data. I would like to
create some new table fields that aggregate these answers into something
analyzable. For example, a separate new field that takes "yes, often,
sometimes, usually, no, never" and converts these to "yes" and "no". Or
takes a list of related symptoms and groups them by my definitions, or takes
3 different common misspellings and defines them as one disease, or outputs
gestational periods into premature vs full term, etc. How do I make a new
field that fills itself in based on the value in another field?

New database updates are downloaded every few weeks so I'd like to not have
to redo this each time.

Thanks much,
Linda

This doesn't answer your question, but you might find the following of
interest since it involves related symptoms:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/13f0cd414aad3a0f

In it, I said:

"Clicking the checkboxes in SubformSymptoms will requery cbxDiseases to
show only the diseases common to all the symptoms checked."

James A. Fortune
(e-mail address removed)