Calculated field in a Form

[Guest]

I have a calculated "Total" field in my form. It calculates just fine but,
the value will not populate to the table. I open the table, see the field but
it remains BLANK.
Need assistance, s.v.p

 

[Guest]

There is no point on saving the value of a calculated field, you can always
achieve that using a query

In a query:
NewFieldName: [Field1]+[Field2]

Saving a calculated field, mean that you'll have to maintain it, and it can
cause mistakes.
e.g: What will happen if someone will update one of the values directly in
the table? the caculated field will be wrong.

A query will always give the right and most current resault
If you need help on how to use the query to calculate the field, please
explain what you need to do.

 

[John W. Vinson]

I have a calculated "Total" field in my form. It calculates just fine but,
the value will not populate to the table. I open the table, see the field but
it remains BLANK.
Need assistance, s.v.p

It is blank, and that's the way it should be. The Total field should simply
NOT EXIST in the table.

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_takeuti]

It is blank, and that's the way it should be. The Total field should
simply
NOT EXIST in the table.

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]

 

[Guest]

Why does everyone keep saying that there is no reason to save the result of a
calculated field? I will need the results of my calculated fields next year
when I do my 1st quarter reports. I will have to compare sales with this
year's 1st quarter. The calculations are done in a query. But I need to
save the results somehow, before I can enter the data for the 2nd quarter.
Right now I am creating a field in the table "1stqtr2007" and manually
entering the results of the calculated field into this field. There are over
200 customers for some of the lines we sell. That's a lot of mundane data
entry.
I have to be able to recreate quarterly reports at my boss' whim, and also I
will need this information next year. Otherwise I would be re-entering all
of the data from this year (work already done) in order to have a comparison
to next year's 1st quarter data.
There has to be a way to save these values without manually entering them
into another field. I don't want them to change every time new data is
entered into the year to date field. I absolutely need that info to remain
static. Help!

There is no point on saving the value of a calculated field, you can always
achieve that using a query

In a query:
NewFieldName: [Field1]+[Field2]

Saving a calculated field, mean that you'll have to maintain it, and it can
cause mistakes.
e.g: What will happen if someone will update one of the values directly in
the table? the caculated field will be wrong.

A query will always give the right and most current resault
If you need help on how to use the query to calculate the field, please
explain what you need to do.

--
Good Luck
BS"D

I have a calculated "Total" field in my form. It calculates just fine but,
the value will not populate to the table. I open the table, see the field but
it remains BLANK.
Need assistance, s.v.p

 

[Tom Wannabe]

these kids just don't understand how to use calculated fields

they're scared of it because they think that it introduces complexity-- but
they are wrong

by all means store calculated values if it makes your life easier or your
reports faster

Why does everyone keep saying that there is no reason to save the result
of a
calculated field? I will need the results of my calculated fields next
year
when I do my 1st quarter reports. I will have to compare sales with this
year's 1st quarter. The calculations are done in a query. But I need to
save the results somehow, before I can enter the data for the 2nd quarter.
Right now I am creating a field in the table "1stqtr2007" and manually
entering the results of the calculated field into this field. There are
over
200 customers for some of the lines we sell. That's a lot of mundane data
entry.
I have to be able to recreate quarterly reports at my boss' whim, and also
I
will need this information next year. Otherwise I would be re-entering
all
of the data from this year (work already done) in order to have a
comparison
to next year's 1st quarter data.
There has to be a way to save these values without manually entering them
into another field. I don't want them to change every time new data is
entered into the year to date field. I absolutely need that info to
remain
static. Help!

There is no point on saving the value of a calculated field, you can
always
achieve that using a query

In a query:
NewFieldName: [Field1]+[Field2]

Saving a calculated field, mean that you'll have to maintain it, and it
can
cause mistakes.
e.g: What will happen if someone will update one of the values directly
in
the table? the caculated field will be wrong.

A query will always give the right and most current resault
If you need help on how to use the query to calculate the field, please
explain what you need to do.

--
Good Luck
BS"D

I have a calculated "Total" field in my form. It calculates just fine
but,
the value will not populate to the table. I open the table, see the
field but
it remains BLANK.
Need assistance, s.v.p

 

[BruceM]

They keep saying it because it's so. You are thinking in spreadsheet terms.
A new field for each quarter is not correct design. It also greatly limits
your flexibility.

If you create a query based on a table, you can use something like this for
the criteria for a date field (I will call it DateField):
Year([DateField])=[Enter year] And DatePart("q",[DateField])=[Enter quarter]
you will be prompted for a year and a quarter. The quarter will be literal
(1st quarter is Jan, Feb, March), but sometimes the start and end of a
quarter are taken to be a Monday and a Sunday, or something like that, so
that there may be some April dates in the first quarter. You can adjust to
that. You can also specify a date range, or look at the first quarter for
all years, or whatever you like. A crosstab query can provide a quarter by
quarter sum, or you can group reports by quarter with the quarter's total
included, or you can mix and match the above. You can do lots of other
things, too, with a proper design.

Your database's design may be holding you back. Without knowing something
about the database's structure and purpose it is difficult to offer specific
suggestions, but keep in mine that a table contains information about a
single real-world entity. Customer information should be stored in its own
table. Transactions with that customer are stored in a separate, related
table. A transaction is not an attribute of a customer, but rather one of
many such events that are related to the customer.

Take a look at this site, particularly the Design section of "Tips for
Casual Users".
http://allenbrowne.com/tips.html
There are links on the right side of the page that can guide you to more
information and explanations.

Why does everyone keep saying that there is no reason to save the result
of a
calculated field? I will need the results of my calculated fields next
year
when I do my 1st quarter reports. I will have to compare sales with this
year's 1st quarter. The calculations are done in a query. But I need to
save the results somehow, before I can enter the data for the 2nd quarter.
Right now I am creating a field in the table "1stqtr2007" and manually
entering the results of the calculated field into this field. There are
over
200 customers for some of the lines we sell. That's a lot of mundane data
entry.
I have to be able to recreate quarterly reports at my boss' whim, and also
I
will need this information next year. Otherwise I would be re-entering
all
of the data from this year (work already done) in order to have a
comparison
to next year's 1st quarter data.
There has to be a way to save these values without manually entering them
into another field. I don't want them to change every time new data is
entered into the year to date field. I absolutely need that info to
remain
static. Help!

There is no point on saving the value of a calculated field, you can
always
achieve that using a query

In a query:
NewFieldName: [Field1]+[Field2]

Saving a calculated field, mean that you'll have to maintain it, and it
can
cause mistakes.
e.g: What will happen if someone will update one of the values directly
in
the table? the caculated field will be wrong.

A query will always give the right and most current resault
If you need help on how to use the query to calculate the field, please
explain what you need to do.

--
Good Luck
BS"D

I have a calculated "Total" field in my form. It calculates just fine
but,
the value will not populate to the table. I open the table, see the
field but
it remains BLANK.
Need assistance, s.v.p

 

[BruceM]

Ignore this person. He has become oh-so-clever and started using a variety
of names, but the message source is the same as for a number of other
postings from a person with a demonstrated ignorance of Access and too much
time on his hands.

these kids just don't understand how to use calculated fields

they're scared of it because they think that it introduces complexity--
but they are wrong

by all means store calculated values if it makes your life easier or your
reports faster

Why does everyone keep saying that there is no reason to save the result
of a
calculated field? I will need the results of my calculated fields next
year
when I do my 1st quarter reports. I will have to compare sales with this
year's 1st quarter. The calculations are done in a query. But I need to
save the results somehow, before I can enter the data for the 2nd
quarter.
Right now I am creating a field in the table "1stqtr2007" and manually
entering the results of the calculated field into this field. There are
over
200 customers for some of the lines we sell. That's a lot of mundane
data
entry.
I have to be able to recreate quarterly reports at my boss' whim, and
also I
will need this information next year. Otherwise I would be re-entering
all
of the data from this year (work already done) in order to have a
comparison
to next year's 1st quarter data.
There has to be a way to save these values without manually entering them
into another field. I don't want them to change every time new data is
entered into the year to date field. I absolutely need that info to
remain
static. Help!

There is no point on saving the value of a calculated field, you can
always
achieve that using a query

In a query:
NewFieldName: [Field1]+[Field2]

Saving a calculated field, mean that you'll have to maintain it, and it
can
cause mistakes.
e.g: What will happen if someone will update one of the values directly
in
the table? the caculated field will be wrong.

A query will always give the right and most current resault
If you need help on how to use the query to calculate the field, please
explain what you need to do.

--
Good Luck
BS"D


:

I have a calculated "Total" field in my form. It calculates just fine
but,
the value will not populate to the table. I open the table, see the
field but
it remains BLANK.
Need assistance, s.v.p

 

[BruceM]

I should add that there are rare legitimate reasons for storing a calculated
field, but I doubt you have that situation.

Why does everyone keep saying that there is no reason to save the result
of a
calculated field? I will need the results of my calculated fields next
year
when I do my 1st quarter reports. I will have to compare sales with this
year's 1st quarter. The calculations are done in a query. But I need to
save the results somehow, before I can enter the data for the 2nd quarter.
Right now I am creating a field in the table "1stqtr2007" and manually
entering the results of the calculated field into this field. There are
over
200 customers for some of the lines we sell. That's a lot of mundane data
entry.
I have to be able to recreate quarterly reports at my boss' whim, and also
I
will need this information next year. Otherwise I would be re-entering
all
of the data from this year (work already done) in order to have a
comparison
to next year's 1st quarter data.
There has to be a way to save these values without manually entering them
into another field. I don't want them to change every time new data is
entered into the year to date field. I absolutely need that info to
remain
static. Help!

There is no point on saving the value of a calculated field, you can
always
achieve that using a query

In a query:
NewFieldName: [Field1]+[Field2]

Saving a calculated field, mean that you'll have to maintain it, and it
can
cause mistakes.
e.g: What will happen if someone will update one of the values directly
in
the table? the caculated field will be wrong.

A query will always give the right and most current resault
If you need help on how to use the query to calculate the field, please
explain what you need to do.

--
Good Luck
BS"D

I have a calculated "Total" field in my form. It calculates just fine
but,
the value will not populate to the table. I open the table, see the
field but
it remains BLANK.
Need assistance, s.v.p