value of a control based on values in multiple other controls

[Tim Colbourn]

Hi,

I'm not sure whether this would be possible but I want to set the value of a
control with the input mask 9-99-9-99-999 to automatically be determined by
the values inputted into 5 other controls previously (one for each set of
numbers in the input mask).

In detail the control to be set is Unique ID (input mask 9-99-9-99-999) and
this should be automatically determined by what is entered in the following
fields:

[District] This is the first number (1, 2 or 3)
[Nearest Health Facility] This is the second part a two digit number (based
on a list box of health facilities)
[HSA] This is the third part a one digit number based on the name of the HSA
(i need to work out a way of automatically numbering new names that are
filled in)
[Volunteer] This is the fourth part, a two digit number based on the name of
the volunteer (again I need to work out a way of automatically numbering new
names that are filled in)
[Name of head of household],[first name],[last name],[alternative name] The
last part, a three digit number of the woman in question (should be a unique
number based on what is filled in the four different names controls - again
this is another problem which might be tricky).

Can you help with this problem?? (also any help on the automatic unique
numbering of based on the entered names, as described in brackets above,
would be helpful!)

Cheers,

Tim

 

[tina]

if you're wanting to *display* the value, you should be able to use an
expression which references those other five controls in the form. if you
want to *store* the value in the form's underlying table, then offhand i'd
say DON'T. when the five values are already stored in five fields in the
record, concatenating them and storing them in a sixth field in the record
is an unnecessary duplication. it violates normalization rules, and can
cause a lot of headaches in terms of data maintenance.

hth

 

[Tim Colbourn]

Hi Tina,

Thanks for your response. However, could you provide a bit more explanation...

I would like to store the value as it will be used to link the information
in the five fields into one unique value which I will then used in a subform
for identifying the person. I don't see it is unnecessary duplication.

Why would it violate normalization rules? and what kind of problems would it
thow up in terms of data maintenance??

Also could you give me an example of the kind of expression that would
reference the other controls in the way I want (adding, subtracting etc.
won't work as I want each number from the other controls displayed / recorded
in the format specified below)?

And finally do you have any thoughts on the other problem of automatically
generating a new number based on the contents of three different controls
containing a first name and surname and an alternative name (see below)?

Thanks for any help you can provide,

Tim

if you're wanting to *display* the value, you should be able to use an
expression which references those other five controls in the form. if you
want to *store* the value in the form's underlying table, then offhand i'd
say DON'T. when the five values are already stored in five fields in the
record, concatenating them and storing them in a sixth field in the record
is an unnecessary duplication. it violates normalization rules, and can
cause a lot of headaches in terms of data maintenance.

hth

Hi,

I'm not sure whether this would be possible but I want to set the value of a
control with the input mask 9-99-9-99-999 to automatically be determined by
the values inputted into 5 other controls previously (one for each set of
numbers in the input mask).

In detail the control to be set is Unique ID (input mask 9-99-9-99-999) and
this should be automatically determined by what is entered in the following
fields:

[District] This is the first number (1, 2 or 3)
[Nearest Health Facility] This is the second part a two digit number (based
on a list box of health facilities)
[HSA] This is the third part a one digit number based on the name of the HSA
(i need to work out a way of automatically numbering new names that are
filled in)
[Volunteer] This is the fourth part, a two digit number based on the name of
the volunteer (again I need to work out a way of automatically numbering new
names that are filled in)
[Name of head of household],[first name],[last name],[alternative name] The
last part, a three digit number of the woman in question (should be a unique
number based on what is filled in the four different names controls - again
this is another problem which might be tricky).

Can you help with this problem?? (also any help on the automatic unique
numbering of based on the entered names, as described in brackets above,
would be helpful!)

Cheers,

Tim

 

[tina]

if you have fields A, B, C, D and E with values 1, 2, 3, 4 and 5, then
adding a sixth field to store 1-2-3-4-5 is a duplication of data, which
violates the principles of relational design - the "a single place for each
thing, and everything in its' place" concept. also, the sixth field is a
calculated value, which should rarely be stored as hard data (i rarely say
"never", but it's close here). when you store calculated data, you must also
make sure that *every* possible circumstance where any "feeder" field is
updated also includes a method to update the calculated value for
consistency. for practical purposes, this is just about impossible to do.
it's also a waste of disk space, and - if the data is stored on a server - a
waste of bandwidth in retrieving the extra data. you can google these
newsgroups on the issue of storing calculated values in a table; you'll find
that the overwhelming consensus in these newsgroups is that it is poor
design.

if you want to use those five fields as an unique identifier to a child
table, then put five corresponding fields in the child table - there's no
reason why you can't use a multi-field key.

to display concatenated field values in a form, make sure the fields are
included in the form's RecordSource, and write an expression in a textbox
control's ControlSource property, such as

=[A] & "-" & & "-" & [C] & "-" & [D] & "-" & [E]

And finally do you have any thoughts on the other problem of automatically
generating a new number based on the contents of three different controls
containing a first name and surname and an alternative name (see below)?

i assume you're referring to your first post:

[HSA] This is the third part a one digit number based on the name of the HSA
(i need to work out a way of automatically numbering new names that are
filled in)
[Volunteer] This is the fourth part, a two digit number based on the

name

of
the volunteer (again I need to work out a way of automatically numbering new
names that are filled in)
[Name of head of household],[first name],[last name],[alternative name] The
last part, a three digit number of the woman in question (should be a unique
number based on what is filled in the four different names controls - again
this is another problem which might be tricky).

it's not clear to me what you're doing here. do you have a table listing all
HSA's? if so, each one should already have a primary key value that can be
used to identify it in other tables as needed. ditto volunteers, and
probably ditto the "woman in question". maybe you can explain the real-world
process you're tracking in some detail, and also post your
tables/relationships design (each table name followed by the field names,
with the primary key and foreign keys designated) - so we have a better
picture of what you're doing before offering more specific suggestions.

hth

Hi Tina,

Thanks for your response. However, could you provide a bit more explanation...

I would like to store the value as it will be used to link the information
in the five fields into one unique value which I will then used in a subform
for identifying the person. I don't see it is unnecessary duplication.

Why would it violate normalization rules? and what kind of problems would it
thow up in terms of data maintenance??

Also could you give me an example of the kind of expression that would
reference the other controls in the way I want (adding, subtracting etc.
won't work as I want each number from the other controls displayed / recorded
in the format specified below)?

And finally do you have any thoughts on the other problem of automatically
generating a new number based on the contents of three different controls
containing a first name and surname and an alternative name (see below)?

Thanks for any help you can provide,

Tim

if you're wanting to *display* the value, you should be able to use an
expression which references those other five controls in the form. if you
want to *store* the value in the form's underlying table, then offhand i'd
say DON'T. when the five values are already stored in five fields in the
record, concatenating them and storing them in a sixth field in the record
is an unnecessary duplication. it violates normalization rules, and can
cause a lot of headaches in terms of data maintenance.

hth

Hi,

I'm not sure whether this would be possible but I want to set the

value of
a

control with the input mask 9-99-9-99-999 to automatically be

determined
by

the values inputted into 5 other controls previously (one for each set of
numbers in the input mask).

In detail the control to be set is Unique ID (input mask

9-99-9-99-999)
and

this should be automatically determined by what is entered in the following
fields:

[District] This is the first number (1, 2 or 3)
[Nearest Health Facility] This is the second part a two digit number (based
on a list box of health facilities)
[HSA] This is the third part a one digit number based on the name of

the
HSA

(i need to work out a way of automatically numbering new names that are
filled in)
[Volunteer] This is the fourth part, a two digit number based on the

name
of

the volunteer (again I need to work out a way of automatically

numbering
new

names that are filled in)
[Name of head of household],[first name],[last name],[alternative

name]
The

last part, a three digit number of the woman in question (should be a unique
number based on what is filled in the four different names controls - again
this is another problem which might be tricky).

Can you help with this problem?? (also any help on the automatic unique
numbering of based on the entered names, as described in brackets above,
would be helpful!)

Cheers,

Tim

 

[Tim Colbourn]

Thanks Tina,

I will have a think further as to how to deal with the issue of a unique ID
(my main idea to have it was so that it would be easier to trace records, as
well as linking to the subform). Maybe I will make a switchboard or something
for entering new information for the same person...

Regarding the second problem (stated above), you are indeed correct that I
will have a table listing all HSA and volunteers. However for the women I
won't as new women will be being entered into the database every month.

To explain further the database is for surviellance of pregnnat women in
Malawi and is intended to record all information on all pregancies, births
and (maternal and neonatal) deaths. The volunteers in the villages record the
information (each pregnant women, birth and death) with the help of the HSAs
(Health Surveillance Assistants). Each HSA works with a number of volunteers
(typically around 10). A list of all the volunteers and HSAs names is being
complied and will be contained in a table that can be referenced.

However, the number of pregnant women that each volunteer records (from the
volunteers village / villages they are doing the surveillance in) will
increase every month as more pregnancies occur. Each new women recorded will
then need a new 'record' (the subform, linked to the main for by the unique
ID number, or maybe as you are now suggesting by all the fields that I
intended to use to make the unique ID number). This subform will ultimately
contain several rows of information (records) e.g. one for August saying the
woman is 8 months pregnant, one for September saying she is 9 months
pregnant, one for October saying she gave birth to a liveborn baby, and one
for November saying that the baby died at 3 weeks old.

For each pregnant women four names are recorded in order to ensure that each
women is separately identifiable, and to ensure that the same women is being
tracked from one month to the next (ensure follow-up). The names are 'head of
household'; 'first name' 'last name' 'alternative name'. It is not compulsory
for all four names to be recorded for each women (though usually the first
three will be).

So my question is whether it would be possible to automatically assign each
new women a unique number (to be stored in a table in a control that will
also be visible on the form) based on the uniqueness of her four names when
she is entered into a new record? Will doing this be similar to making the
unique ID number (i.e. not advisable?)?

If the women is linked to a specific volunteer (named and numbered in a
table), maybe the unique numbers for the new women would only have to be
unique with respect to the particular volunteer (each volunteer is only
likely to identify a couple of hundred pregnant women over the two year
period of study)?

Thanks for your help,

Tim

 

[tina]

hmm, okay. i think you need to back up and take a look at your tables
structure. but prior to doing that, i really urge you to read up on
relational design principles. you have some misconceptions about how Access
works as a relational data management tool, which are going to cause you
some headaches, i'm afraid. to get started, suggest you go to
http://home.att.net/~california.db/tips.html and read at least the first 4
tips, though i think you'll find the next 5 helpful at this point, also.

hth

 

[Tim Colbourn]

Thanks for the link. Have been doing some more reading up too.
Am currently waiting on some data before I progress with the database design
so will continue with it in a week or two, if i come across any problems i'll
be in touch...
Cheers,
Tim

 

[alan menikoff]

HOW D O I CEASE RECEIVING THESE GRO UP MAILS"??