adding fields

  • Thread starter Thread starter jpnyc
  • Start date Start date
J

jpnyc

I'm a novice at access and using Access 2000
There is probably a simple answer, but I can't make the operation work so
specific details would probably help me considerably.

I have Four fields on a form: F1, F2, F3, F4
Say I populate the fields F1=0, F2=50 and F3=100

I would like F4 to add F1+F2+F3 as the fields are populated ----> 150

Okay now the trick, say F2 is left blank. so I want F4 to add F1 and F3-->100

Trick #2: I would also like F4 to divide the total sum by the number of
fields populated. (i.e 150/3 or 100/2)


Thanks in advance
 
So which do you want F4 to show? The total or the average?

Basically what you need is a function you can call from the After Update
event of F1, F2, and F3. It would be something like this:

Private Function CalcControls() As Boolean
Dim dblTotal As Double
Dim lngCounter as Long

With Me
dblTotal = Nz(.F1, 0) + Nz(.F2, 0) + Nz(.F3,0)
If Not IsNull(.F1) Then
lngCounter = 1
End If
If Not IsNull(.F2) Then
lngCounter = lngCounter + 1
End If
If Not IsNull(.F1) Then
lngCounter = lngCounter +1
End If

'To get the total
.F4 = dblTotal

'To get the arverage
If lngCounter = 0 then
.F4 = dblTotal
Else
.F4 = dblTotal / lngCounter
End If
End With
End Function
 
First things first ... let's agree that even though you have F4 on your form
(and by the way, on forms they are termed "controls", in tables they are
termed "fields" -- a "control" is like a window through which you see the
"field"'s value), you are NOT trying to store that "calculated value" in
your form's underlying table. It's rarely necessary and based on your
description, this is not one of the exceptions.

Next, "blank" may not = "blank". Even though you don't see anything in the
control, this could be because the underlying field has no value (i.e.,
Null), or because the underlying field has a "zero-length string" (zls), or
because the underlying field has spaces, or ... Those all look the same to
the human eye. So look into using the Nz() function, which converts a Null
to whatever you want (say, zero!). You could use something like:
Nz([F1],0)

Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum of
the (non-null) values.

But "0" can be meaningful! How many dollars do you have left in your
wallet? "0"! So how you do this will depend on what you are measuring.

You can put an expression in the Control Source property of your [F4] on the
form in design view, and use the Nz() function, and other functions that
count the number of non-null values, but there's an underlying potential
issue.

If you have multiple fields in your table to store numbers that can be added
together like this, you may be committing spreadsheet on Access. Using
multiple columns to capture numbers, then adding them together is something
you'd do like this if limited to using a spreadsheet. But Access is a
relational database, not a spreadsheet.

Consider posting a bit more description about where these numbers came from
and why you want to do this. Folks here may be able to offer ways for you
to get more out of the tools Access offers.

Regards

Jeff Boyce
Microsoft Access MVP
 
Thanks for your responses. I will attempt to explain what I am doing so bear
with me. Meanwhile, I will play with the advice I was given.

I want to create a database for a Parkinson's research project. Basically,
as the study subjects complete tasks(fields/controls), they each receive a
score based on the time it took to complete.
If they go over the time limit the recieve a score "0" [i.e unscramble
letter] (task1= F1 = 0).
If they complete above the 85th percentile, they score "100"[ i.e draw an
square]( task3= F3 = 100),
if they perform average they score "50" [i.e touch left knee with right
hand] (task2= F2=50), .
etc.etc.
If they don't want to do the task, I can't give them a score and leave the
value blank [hop on one foot = task8 = " " = null), .

So I need to tally the scores of each task and show the average for each
category (i.e mental (fields/controls) = F4 or physical limitation
(fields/controls)=F5, ) by dividing the total sum of tasks by the number of
tasks completed. {maybe the calculation belongs in the query but I still need
to account for null}

F1(0)+F2(50)+F3(100) = 150 -->150/3 = 50(F4) mental
F1(0)+F3(100)+F8(null) = 100-->100/2 = 50(F5) physical

Now for each patient visit, I would enter these scores for each task on a
new form and allow the database to calculate the scores entered and average
the current visit categories (mental and physical)

Another small problem I see is that my primary key has to be a random
generated number to separate each visit(new forms) which I think I
accomplished. Each patient has at least three visits, so any identifier to
them can't be used as the primary key because of repetition.

Now with access, I hope to eliminate the hand writing/paper searching and
store the values for each visit to monitor the progression.

I then developed a query for each patient (i.e query Smith and query Rivers)
to restrict the patient value

and then

Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith
7/1/09.

Sorry to essay, but I will play with the advice already given. Any further
advice would be appreciated. Thanks again for the assistance

Jeff Boyce said:
First things first ... let's agree that even though you have F4 on your form
(and by the way, on forms they are termed "controls", in tables they are
termed "fields" -- a "control" is like a window through which you see the
"field"'s value), you are NOT trying to store that "calculated value" in
your form's underlying table. It's rarely necessary and based on your
description, this is not one of the exceptions.

Next, "blank" may not = "blank". Even though you don't see anything in the
control, this could be because the underlying field has no value (i.e.,
Null), or because the underlying field has a "zero-length string" (zls), or
because the underlying field has spaces, or ... Those all look the same to
the human eye. So look into using the Nz() function, which converts a Null
to whatever you want (say, zero!). You could use something like:
Nz([F1],0)

Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum of
the (non-null) values.

But "0" can be meaningful! How many dollars do you have left in your
wallet? "0"! So how you do this will depend on what you are measuring.

You can put an expression in the Control Source property of your [F4] on the
form in design view, and use the Nz() function, and other functions that
count the number of non-null values, but there's an underlying potential
issue.

If you have multiple fields in your table to store numbers that can be added
together like this, you may be committing spreadsheet on Access. Using
multiple columns to capture numbers, then adding them together is something
you'd do like this if limited to using a spreadsheet. But Access is a
relational database, not a spreadsheet.

Consider posting a bit more description about where these numbers came from
and why you want to do this. Folks here may be able to offer ways for you
to get more out of the tools Access offers.

Regards

Jeff Boyce
Microsoft Access MVP



jpnyc said:
I'm a novice at access and using Access 2000
There is probably a simple answer, but I can't make the operation work so
specific details would probably help me considerably.

I have Four fields on a form: F1, F2, F3, F4
Say I populate the fields F1=0, F2=50 and F3=100

I would like F4 to add F1+F2+F3 as the fields are populated ----> 150

Okay now the trick, say F2 is left blank. so I want F4 to add F1 and
F3-->100

Trick #2: I would also like F4 to divide the total sum by the number of
fields populated. (i.e 150/3 or 100/2)


Thanks in advance
Click to expand...
Click to expand...
 
Based on your further elaboration, you really need to spend the time
deciding whether to 'add up' your scores using a tool designed to add up
values (e.g., Excel), or whether you want to learn relational database
design, Access tips/tricks, graphical user interface design and application
development. You will need experience with all four of those if you want to
get good use of THAT tool (MS Access).

What's more important, adding scores or learning application development
using relational databases?

How soon must this be completed? What's the budget?

This is a "pay now or pay later" situation. If you try to stuff multiple
(repeating) fields into an Access table, you and Access will have to work
overtime to overcome data for which Access is not optimized (i.e., 'sheet
data).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

jpnyc said:
Thanks for your responses. I will attempt to explain what I am doing so
bear
with me. Meanwhile, I will play with the advice I was given.

I want to create a database for a Parkinson's research project. Basically,
as the study subjects complete tasks(fields/controls), they each receive a
score based on the time it took to complete.
If they go over the time limit the recieve a score "0" [i.e unscramble
letter] (task1= F1 = 0).
If they complete above the 85th percentile, they score "100"[ i.e draw an
square]( task3= F3 = 100),
if they perform average they score "50" [i.e touch left knee with right
hand] (task2= F2=50), .
etc.etc.
If they don't want to do the task, I can't give them a score and leave the
value blank [hop on one foot = task8 = " " = null), .

So I need to tally the scores of each task and show the average for each
category (i.e mental (fields/controls) = F4 or physical limitation
(fields/controls)=F5, ) by dividing the total sum of tasks by the number
of
tasks completed. {maybe the calculation belongs in the query but I still
need
to account for null}

F1(0)+F2(50)+F3(100) = 150 -->150/3 = 50(F4) mental
F1(0)+F3(100)+F8(null) = 100-->100/2 = 50(F5) physical

Now for each patient visit, I would enter these scores for each task on a
new form and allow the database to calculate the scores entered and
average
the current visit categories (mental and physical)

Another small problem I see is that my primary key has to be a random
generated number to separate each visit(new forms) which I think I
accomplished. Each patient has at least three visits, so any identifier to
them can't be used as the primary key because of repetition.

Now with access, I hope to eliminate the hand writing/paper searching and
store the values for each visit to monitor the progression.

I then developed a query for each patient (i.e query Smith and query
Rivers)
to restrict the patient value

and then

Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith
7/1/09.

Sorry to essay, but I will play with the advice already given. Any further
advice would be appreciated. Thanks again for the assistance

Jeff Boyce said:
First things first ... let's agree that even though you have F4 on your
form
(and by the way, on forms they are termed "controls", in tables they are
termed "fields" -- a "control" is like a window through which you see the
"field"'s value), you are NOT trying to store that "calculated value" in
your form's underlying table. It's rarely necessary and based on your
description, this is not one of the exceptions.

Next, "blank" may not = "blank". Even though you don't see anything in
the
control, this could be because the underlying field has no value (i.e.,
Null), or because the underlying field has a "zero-length string" (zls),
or
because the underlying field has spaces, or ... Those all look the same
to
the human eye. So look into using the Nz() function, which converts a
Null
to whatever you want (say, zero!). You could use something like:
Nz([F1],0)

Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum
of
the (non-null) values.

But "0" can be meaningful! How many dollars do you have left in your
wallet? "0"! So how you do this will depend on what you are measuring.

You can put an expression in the Control Source property of your [F4] on
the
form in design view, and use the Nz() function, and other functions that
count the number of non-null values, but there's an underlying potential
issue.

If you have multiple fields in your table to store numbers that can be
added
together like this, you may be committing spreadsheet on Access. Using
multiple columns to capture numbers, then adding them together is
something
you'd do like this if limited to using a spreadsheet. But Access is a
relational database, not a spreadsheet.

Consider posting a bit more description about where these numbers came
from
and why you want to do this. Folks here may be able to offer ways for
you
to get more out of the tools Access offers.

Regards

Jeff Boyce
Microsoft Access MVP



jpnyc said:
I'm a novice at access and using Access 2000
There is probably a simple answer, but I can't make the operation work
so
specific details would probably help me considerably.

I have Four fields on a form: F1, F2, F3, F4
Say I populate the fields F1=0, F2=50 and F3=100

I would like F4 to add F1+F2+F3 as the fields are populated ----> 150

Okay now the trick, say F2 is left blank. so I want F4 to add F1 and
F3-->100

Trick #2: I would also like F4 to divide the total sum by the number of
fields populated. (i.e 150/3 or 100/2)


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Thanks Jeff for your advice. There is no pay for this project as I ama
medical resident and this is part of my research thesis. I will have medical
students administering tests and making phone calls, so I can focus on the
literature development and advancement for the field. My purpose for access
is to have quick synopsis of the patients and easy data entry for the
students. Time to complete is the sooner the better, but no deadline. Just
add the hard copy to the stack of papers already here. Thanks again.

Jeff Boyce said:
Based on your further elaboration, you really need to spend the time
deciding whether to 'add up' your scores using a tool designed to add up
values (e.g., Excel), or whether you want to learn relational database
design, Access tips/tricks, graphical user interface design and application
development. You will need experience with all four of those if you want to
get good use of THAT tool (MS Access).

What's more important, adding scores or learning application development
using relational databases?

How soon must this be completed? What's the budget?

This is a "pay now or pay later" situation. If you try to stuff multiple
(repeating) fields into an Access table, you and Access will have to work
overtime to overcome data for which Access is not optimized (i.e., 'sheet
data).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

jpnyc said:
Thanks for your responses. I will attempt to explain what I am doing so
bear
with me. Meanwhile, I will play with the advice I was given.

I want to create a database for a Parkinson's research project. Basically,
as the study subjects complete tasks(fields/controls), they each receive a
score based on the time it took to complete.
If they go over the time limit the recieve a score "0" [i.e unscramble
letter] (task1= F1 = 0).
If they complete above the 85th percentile, they score "100"[ i.e draw an
square]( task3= F3 = 100),
if they perform average they score "50" [i.e touch left knee with right
hand] (task2= F2=50), .
etc.etc.
If they don't want to do the task, I can't give them a score and leave the
value blank [hop on one foot = task8 = " " = null), .

So I need to tally the scores of each task and show the average for each
category (i.e mental (fields/controls) = F4 or physical limitation
(fields/controls)=F5, ) by dividing the total sum of tasks by the number
of
tasks completed. {maybe the calculation belongs in the query but I still
need
to account for null}

F1(0)+F2(50)+F3(100) = 150 -->150/3 = 50(F4) mental
F1(0)+F3(100)+F8(null) = 100-->100/2 = 50(F5) physical

Now for each patient visit, I would enter these scores for each task on a
new form and allow the database to calculate the scores entered and
average
the current visit categories (mental and physical)

Another small problem I see is that my primary key has to be a random
generated number to separate each visit(new forms) which I think I
accomplished. Each patient has at least three visits, so any identifier to
them can't be used as the primary key because of repetition.

Now with access, I hope to eliminate the hand writing/paper searching and
store the values for each visit to monitor the progression.

I then developed a query for each patient (i.e query Smith and query
Rivers)
to restrict the patient value

and then

Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith
7/1/09.

Sorry to essay, but I will play with the advice already given. Any further
advice would be appreciated. Thanks again for the assistance

Jeff Boyce said:
First things first ... let's agree that even though you have F4 on your
form
(and by the way, on forms they are termed "controls", in tables they are
termed "fields" -- a "control" is like a window through which you see the
"field"'s value), you are NOT trying to store that "calculated value" in
your form's underlying table. It's rarely necessary and based on your
description, this is not one of the exceptions.

Next, "blank" may not = "blank". Even though you don't see anything in
the
control, this could be because the underlying field has no value (i.e.,
Null), or because the underlying field has a "zero-length string" (zls),
or
because the underlying field has spaces, or ... Those all look the same
to
the human eye. So look into using the Nz() function, which converts a
Null
to whatever you want (say, zero!). You could use something like:
Nz([F1],0)

Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the sum
of
the (non-null) values.

But "0" can be meaningful! How many dollars do you have left in your
wallet? "0"! So how you do this will depend on what you are measuring.

You can put an expression in the Control Source property of your [F4] on
the
form in design view, and use the Nz() function, and other functions that
count the number of non-null values, but there's an underlying potential
issue.

If you have multiple fields in your table to store numbers that can be
added
together like this, you may be committing spreadsheet on Access. Using
multiple columns to capture numbers, then adding them together is
something
you'd do like this if limited to using a spreadsheet. But Access is a
relational database, not a spreadsheet.

Consider posting a bit more description about where these numbers came
from
and why you want to do this. Folks here may be able to offer ways for
you
to get more out of the tools Access offers.

Regards

Jeff Boyce
Microsoft Access MVP



I'm a novice at access and using Access 2000
There is probably a simple answer, but I can't make the operation work
so
specific details would probably help me considerably.

I have Four fields on a form: F1, F2, F3, F4
Say I populate the fields F1=0, F2=50 and F3=100

I would like F4 to add F1+F2+F3 as the fields are populated ----> 150

Okay now the trick, say F2 is left blank. so I want F4 to add F1 and
F3-->100

Trick #2: I would also like F4 to divide the total sum by the number of
fields populated. (i.e 150/3 or 100/2)


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
The point I tried to make is that there is no "sooner" when you start with a
new tool. Access is a relational database. Access is optimized to work
best with well-normalized data.

If these are unfamiliar terms, and if you haven't used Access before, you
could be old and gray before you get done what you want <g>.

If you want/need the sums of some variable number of measurements, the
quick/dirty way to do this is in Excel.

If you have the time, you certainly can do this in Access, but it won't
look/act the same as a spreadsheet (unless, of course, you learn enough
about Access to circumvent the well-designed built-in tools <g>).

I suspect you're describing something like (untested, unproven):

trelTestResults
TestResultID
PersonID (who took the test)
TestID (which test)
TestDate (when)
TestResult (however you measure)

With a table design like this, you could have one person with one or ten or
100 'tests', and adding another test result for that person would mean
adding one more record in the table.

Also, if you introduced a new "test", you'd add it to your "other" table
(tblTest), then use it's ID in the above table to show persons' results on
that new test.

No need to add columns, no need to redesign queries, no need to modify forms
and reports, no need ...!

Best of Luck!

Regards

Jeff Boyce
Microsoft Access MVP

jpnyc said:
Thanks Jeff for your advice. There is no pay for this project as I ama
medical resident and this is part of my research thesis. I will have
medical
students administering tests and making phone calls, so I can focus on the
literature development and advancement for the field. My purpose for
access
is to have quick synopsis of the patients and easy data entry for the
students. Time to complete is the sooner the better, but no deadline. Just
add the hard copy to the stack of papers already here. Thanks again.

Jeff Boyce said:
Based on your further elaboration, you really need to spend the time
deciding whether to 'add up' your scores using a tool designed to add up
values (e.g., Excel), or whether you want to learn relational database
design, Access tips/tricks, graphical user interface design and
application
development. You will need experience with all four of those if you want
to
get good use of THAT tool (MS Access).

What's more important, adding scores or learning application development
using relational databases?

How soon must this be completed? What's the budget?

This is a "pay now or pay later" situation. If you try to stuff multiple
(repeating) fields into an Access table, you and Access will have to work
overtime to overcome data for which Access is not optimized (i.e., 'sheet
data).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

jpnyc said:
Thanks for your responses. I will attempt to explain what I am doing
so
bear
with me. Meanwhile, I will play with the advice I was given.

I want to create a database for a Parkinson's research project.
Basically,
as the study subjects complete tasks(fields/controls), they each
receive a
score based on the time it took to complete.
If they go over the time limit the recieve a score "0" [i.e unscramble
letter] (task1= F1 = 0).
If they complete above the 85th percentile, they score "100"[ i.e draw
an
square]( task3= F3 = 100),
if they perform average they score "50" [i.e touch left knee with right
hand] (task2= F2=50), .
etc.etc.
If they don't want to do the task, I can't give them a score and leave
the
value blank [hop on one foot = task8 = " " = null), .

So I need to tally the scores of each task and show the average for
each
category (i.e mental (fields/controls) = F4 or physical limitation
(fields/controls)=F5, ) by dividing the total sum of tasks by the
number
of
tasks completed. {maybe the calculation belongs in the query but I
still
need
to account for null}

F1(0)+F2(50)+F3(100) = 150 -->150/3 = 50(F4) mental
F1(0)+F3(100)+F8(null) = 100-->100/2 = 50(F5) physical

Now for each patient visit, I would enter these scores for each task
on a
new form and allow the database to calculate the scores entered and
average
the current visit categories (mental and physical)

Another small problem I see is that my primary key has to be a random
generated number to separate each visit(new forms) which I think I
accomplished. Each patient has at least three visits, so any identifier
to
them can't be used as the primary key because of repetition.

Now with access, I hope to eliminate the hand writing/paper searching
and
store the values for each visit to monitor the progression.

I then developed a query for each patient (i.e query Smith and query
Rivers)
to restrict the patient value

and then

Generate a report to show each visit. Smith 1/1/09, Smith 4/1/09, Smith
7/1/09.

Sorry to essay, but I will play with the advice already given. Any
further
advice would be appreciated. Thanks again for the assistance

:

First things first ... let's agree that even though you have F4 on
your
form
(and by the way, on forms they are termed "controls", in tables they
are
termed "fields" -- a "control" is like a window through which you see
the
"field"'s value), you are NOT trying to store that "calculated value"
in
your form's underlying table. It's rarely necessary and based on your
description, this is not one of the exceptions.

Next, "blank" may not = "blank". Even though you don't see anything
in
the
control, this could be because the underlying field has no value
(i.e.,
Null), or because the underlying field has a "zero-length string"
(zls),
or
because the underlying field has spaces, or ... Those all look the
same
to
the human eye. So look into using the Nz() function, which converts a
Null
to whatever you want (say, zero!). You could use something like:
Nz([F1],0)

Now, if you used Nz([F1],0] + Nz([F2],0) + Nz([F3],0), you'd get the
sum
of
the (non-null) values.

But "0" can be meaningful! How many dollars do you have left in your
wallet? "0"! So how you do this will depend on what you are
measuring.

You can put an expression in the Control Source property of your [F4]
on
the
form in design view, and use the Nz() function, and other functions
that
count the number of non-null values, but there's an underlying
potential
issue.

If you have multiple fields in your table to store numbers that can be
added
together like this, you may be committing spreadsheet on Access.
Using
multiple columns to capture numbers, then adding them together is
something
you'd do like this if limited to using a spreadsheet. But Access is a
relational database, not a spreadsheet.

Consider posting a bit more description about where these numbers came
from
and why you want to do this. Folks here may be able to offer ways for
you
to get more out of the tools Access offers.

Regards

Jeff Boyce
Microsoft Access MVP



I'm a novice at access and using Access 2000
There is probably a simple answer, but I can't make the operation
work
so
specific details would probably help me considerably.

I have Four fields on a form: F1, F2, F3, F4
Say I populate the fields F1=0, F2=50 and F3=100

I would like F4 to add F1+F2+F3 as the fields are populated ---->
150

Okay now the trick, say F2 is left blank. so I want F4 to add F1 and
F3-->100

Trick #2: I would also like F4 to divide the total sum by the number
of
fields populated. (i.e 150/3 or 100/2)


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top