Info Not updating in table when calculation done in form.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two fields that I have a calulation in. One is figuring a percentage
of two amounts and the other is figuring number of days between dates. These
fields show on the form and look like they are working perfectly, however,
the information is not being updated to the Table. What am I doing wrong?????
 
You are not doing anything wrong. You don't store calculated values in a
table. You already store the data needed to perform the calculation in your
forms, reports, and queries. Storing the calculated value also would be
redundant. What if one of the values changes? You would then have to
update two fields or risk your data not being "in synch".

Calculations are performed on the fly in your forms, queries, and reports.
The data needed to perform those calculations is stored in the table.
 
I am trying to build this table from the form and keep it updated by the
form. I am doing some calculations in adding fields to get a total in the
form and they are going to the table. What is the difference????
 
I'm not following you.

You should be storing the unique values in the table. Anything that is
calculated using one or more of those values does not need to be stored. It
would be redundant.

To quote one of our MVP's from a previous post...

Storing derived data such as this in your table accomplishes three things:
it wastes disk space; it wastes time (almost any calculation will be MUCH
faster than a disk fetch); and most importantly, it risks data corruption.
If one of the underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect that fact.



Just redo the calculation whenever you need it, either as a calculated field
in a Query or just as you're now doing it -in the control source of a Form
or a Report textbox.



John W. Vinson[MVP]
 
I thought that the form was a way to build the data in a table. I also
thought when you pull up the form that the information that you see is from
the table. Also, it was my impression that the data changed in the form was
save to the table. I wanted to use the table as my base to build reports and
run queries off of it. If I need to do this different in my form please let
me know.
 
You are correct, your form is a way to enter data in the table. But you
only store data you need.

For example, you might have a birthdate field on your form (as I do) and
right next to it you display the person's age in years and months. You
don't save that data, only the birthdate. Everytime you pull up a record on
that form, the person's age will appear. If you store it, it will be wrong
every time a new month pops up.

On a form, I have "First Name" Middle Name" and "Last Name", all of which
are stored in my table. On the second tab of my form, I have a field
like... =[LastName] & ", " [FirstName] & " " & [MiddleName]
This combined data is not stored in my table. On some reports, I might want
to print last name first, on others I might want to print first name then
last. I don't need to store both.

To get closer to your example, I might have to take a certification test
every year. All I have to do is store the date I took the test. I don't
need to ALSO store that date plus one year. When I need to see who is
coming up for a test soon, I just pull all the records where "test date" +
356 days is within 60 days of today. I use a calculated date in my query to
figure out the due date (test date + 365) and then I use criteria to say
that the result needs to be between today and 60 days from today.

Hope that helps.

In answer to your specific questions below, entering data in a form is saved
(if it is a BOUND field). Pulling up a record in a form does display the
data from the form. It can also display calculated values from that data
(such as a person's CURRENT age. Changing data in a form does update the
table (for an bound fields). You can use the table data in a query and can
perform calculations as well in that query. The query (or a table) can be
used as the data source in a report.
 
I understand what you are saying. I will need to use a query to build this
data if I want to pull an report from this database. Calculated fields do
not update themselfs on the table when the calculation are done on the form.
The reason I was not understatnding is that you had to have both fields
filled in for it to do the calculation in this field and these dates would
not change. The same with the percentage.

Thanks for being patient with me and explaining the process.

Rick B said:
You are correct, your form is a way to enter data in the table. But you
only store data you need.

For example, you might have a birthdate field on your form (as I do) and
right next to it you display the person's age in years and months. You
don't save that data, only the birthdate. Everytime you pull up a record on
that form, the person's age will appear. If you store it, it will be wrong
every time a new month pops up.

On a form, I have "First Name" Middle Name" and "Last Name", all of which
are stored in my table. On the second tab of my form, I have a field
like... =[LastName] & ", " [FirstName] & " " & [MiddleName]
This combined data is not stored in my table. On some reports, I might want
to print last name first, on others I might want to print first name then
last. I don't need to store both.

To get closer to your example, I might have to take a certification test
every year. All I have to do is store the date I took the test. I don't
need to ALSO store that date plus one year. When I need to see who is
coming up for a test soon, I just pull all the records where "test date" +
356 days is within 60 days of today. I use a calculated date in my query to
figure out the due date (test date + 365) and then I use criteria to say
that the result needs to be between today and 60 days from today.

Hope that helps.

In answer to your specific questions below, entering data in a form is saved
(if it is a BOUND field). Pulling up a record in a form does display the
data from the form. It can also display calculated values from that data
(such as a person's CURRENT age. Changing data in a form does update the
table (for an bound fields). You can use the table data in a query and can
perform calculations as well in that query. The query (or a table) can be
used as the data source in a report.
--
Rick B



jrp444 said:
I thought that the form was a way to build the data in a table. I also
thought when you pull up the form that the information that you see is
from
the table. Also, it was my impression that the data changed in the form
was
save to the table. I wanted to use the table as my base to build reports
and
run queries off of it. If I need to do this different in my form please
let
me know.
Click to expand...
Click to expand...
 
Back
Top