sum

[Guest]

I would like the 3 fields to be on a form as well. Field 1: user types in a
dollar amount, Field 2: user types in a dollar amount, Field 3: the sum of
Field 1 and Field 2 is there automatically as a default, but the user can
also change the value. And all 3 fields get saved into the table.


Yes it is possible there are a "very" few times when it's a good idea. Most
of the time you should run the calcultation as and when you need it in a form
or query or report.

The reason for this is that if you store a calculation such as "age.
Today - date of birth
The is will only be right today unless you do the calculation every day
(which would take some time on a large DB).

Open a new query based on the table and use the wizard (right click - build)
to help you with the calculation

Good luck

--
Wayne
Manchester, England.

I am creating an Access database and I would like a field in a table to have
a default that automatically gives the numerical sum of two other fields in
the same table, BUT also maintain the ability to edit the field. I would
like the field to just give the sum as a default, not a fixed value.
Example: Field 1 is "A Amount" and Field 2 is "B Amount" and I would like the
sum of "A Amount" and "B Amount" to show up in Field 3, but be
editable/overwritable. It would not work for me to put this expression into
the Default Value for Field 3 in the table. Is this possible?

Did this post answer the question?

 

[Guest]

Hi

It's best if you continue a thread rather than createing a new one with each
answer.

Anyway.

This is a bad idea (to store the 3 sets of data)


I would like the 3 fields to be on a form as well.
No problem. Create a form based on the table (or a query based on the table)
Select View - available fields and drage and drop field1 and field2 into you
form.
Select View - toolbox and create a new text box (unbound - this means it is
not bound to field in your table).
Right click and open the properties box.
In the Data Colomn select the source row
put this =[Field1]+[Field2]

ge the value. And all 3 fields get saved into the table.

Don't store the results shown in the unbound box.

 

[Guest]

Why is it a bad idea? The whole point is that I need to save the data from
all 3 fields in my table. The first 2 fields are dollar amounts and the 3rd
field is the total of those 2, but sometimes it could be lower because of
discounts, deals, etc. That is why it needs to be editable. What you said
sounds good except that I need it all saved in the table. Thank you for your
time.

Hi

It's best if you continue a thread rather than createing a new one with each
answer.

Anyway.

This is a bad idea (to store the 3 sets of data)


I would like the 3 fields to be on a form as well.
No problem. Create a form based on the table (or a query based on the table)
Select View - available fields and drage and drop field1 and field2 into you
form.
Select View - toolbox and create a new text box (unbound - this means it is
not bound to field in your table).
Right click and open the properties box.
In the Data Colomn select the source row
put this =[Field1]+[Field2]

ge the value. And all 3 fields get saved into the table.

Don't store the results shown in the unbound box.


--
Wayne
Manchester, England.

I would like the 3 fields to be on a form as well. Field 1: user types in a
dollar amount, Field 2: user types in a dollar amount, Field 3: the sum of
Field 1 and Field 2 is there automatically as a default, but the user can
also change the value. And all 3 fields get saved into the table.


Yes it is possible there are a "very" few times when it's a good idea. Most
of the time you should run the calcultation as and when you need it in a form
or query or report.

The reason for this is that if you store a calculation such as "age.
Today - date of birth
The is will only be right today unless you do the calculation every day
(which would take some time on a large DB).

Open a new query based on the table and use the wizard (right click - build)
to help you with the calculation

Good luck

--
Wayne
Manchester, England.




Did this post answer the question?

 

[Guest]

In this case you don't want to save the calculated result.

If you need it you can open the form and it will be there. You can print it
off at any time in a report. You can use the result as part of another
calculation in a query, etc.etc.

Don't store the result - you need to store the 2 amounts and that's it.
Really.

It's up to you but it "will" go wrong if you store it. If you change the
amunts - or just one of them the result will not be correct. OK you can
always store it again but whats the point.

As I said there "are" times when you are better storeing a calculation, but
this is not one of them. I run a number of companies databases and I have
only stored the result of 2 calculations. So in many thousands of
calculations going on each day I have coded all the different databases to
store just 2.

Its up to you - in these newgroups people can only give thier ideas - it's
up to you to decide on what to do as only you can see your application.
Maybe someone else will join the thread and say something different in this
case.

My ideas is for you not store the results.


Hope this helps

--
Wayne
Manchester, England.

Why is it a bad idea? The whole point is that I need to save the data from
all 3 fields in my table. The first 2 fields are dollar amounts and the 3rd
field is the total of those 2, but sometimes it could be lower because of
discounts, deals, etc. That is why it needs to be editable. What you said
sounds good except that I need it all saved in the table. Thank you for your
time.

Hi

It's best if you continue a thread rather than createing a new one with each
answer.

Anyway.

This is a bad idea (to store the 3 sets of data)


I would like the 3 fields to be on a form as well.
No problem. Create a form based on the table (or a query based on the table)
Select View - available fields and drage and drop field1 and field2 into you
form.
Select View - toolbox and create a new text box (unbound - this means it is
not bound to field in your table).
Right click and open the properties box.
In the Data Colomn select the source row
put this =[Field1]+[Field2]

ge the value. And all 3 fields get saved into the table.

Don't store the results shown in the unbound box.


--
Wayne
Manchester, England.

I would like the 3 fields to be on a form as well. Field 1: user types in a
dollar amount, Field 2: user types in a dollar amount, Field 3: the sum of
Field 1 and Field 2 is there automatically as a default, but the user can
also change the value. And all 3 fields get saved into the table.


Yes it is possible there are a "very" few times when it's a good idea. Most
of the time you should run the calcultation as and when you need it in a form
or query or report.

The reason for this is that if you store a calculation such as "age.
Today - date of birth
The is will only be right today unless you do the calculation every day
(which would take some time on a large DB).

Open a new query based on the table and use the wizard (right click - build)
to help you with the calculation

Good luck

--
Wayne
Manchester, England.



:

I am creating an Access database and I would like a field in a table to have
a default that automatically gives the numerical sum of two other fields in
the same table, BUT also maintain the ability to edit the field. I would
like the field to just give the sum as a default, not a fixed value.
Example: Field 1 is "A Amount" and Field 2 is "B Amount" and I would like the
sum of "A Amount" and "B Amount" to show up in Field 3, but be
editable/overwritable. It would not work for me to put this expression into
the Default Value for Field 3 in the table. Is this possible?
Did this post answer the question?

 

[Pieter Wijnen]

We're sometimes to religous about storing redundant data.
The rules of a normalized Table does not apply to all Fields in a
Transactional Database
But in this case the reaction was due to the missing bit of information
about discounts
It would however from a DBA's point of view (and a lot others) to store the
discount information in the third field
instead of the calculated value.
The only time you'd want to store calculated values are in a (financial)
transaction table that is not allowed to be updated after it's "locked", ie
the sale's been closed. This may for instance be currency rate or VAT
related calculations.

To start to answer your original question:
Access does not have any table level triggering mechanism that can execute
the Business logic.
You therefore have to make use of routines that is built into every input
mechanism you're using (Forms, Append & Update Queries etc)
Your best bet is therefore to make a Publicly declared VBA Function (or
Procedure) that you invoke in Your input Form's Before_Update Event
And any other place where the involved fields are updated.

In Your Case I'd personally have the Third Field for Discount & Calculate
the sum of All Three Controls on the Form as you've already been advised.

Pieter

Why is it a bad idea? The whole point is that I need to save the data from
all 3 fields in my table. The first 2 fields are dollar amounts and the
3rd
field is the total of those 2, but sometimes it could be lower because of
discounts, deals, etc. That is why it needs to be editable. What you said
sounds good except that I need it all saved in the table. Thank you for
your
time.

Hi

It's best if you continue a thread rather than createing a new one with
each
answer.

Anyway.

This is a bad idea (to store the 3 sets of data)


I would like the 3 fields to be on a form as well.
No problem. Create a form based on the table (or a query based on the
table)
Select View - available fields and drage and drop field1 and field2 into
you
form.
Select View - toolbox and create a new text box (unbound - this means it
is
not bound to field in your table).
Right click and open the properties box.
In the Data Colomn select the source row
put this =[Field1]+[Field2]

ge the value. And all 3 fields get saved into the table.

Don't store the results shown in the unbound box.


--
Wayne
Manchester, England.

I would like the 3 fields to be on a form as well. Field 1: user types
in a
dollar amount, Field 2: user types in a dollar amount, Field 3: the sum
of
Field 1 and Field 2 is there automatically as a default, but the user
can
also change the value. And all 3 fields get saved into the table.


Yes it is possible there are a "very" few times when it's a good idea.
Most
of the time you should run the calcultation as and when you need it in
a form
or query or report.

The reason for this is that if you store a calculation such as "age.
Today - date of birth
The is will only be right today unless you do the calculation every day
(which would take some time on a large DB).

Open a new query based on the table and use the wizard (right click -
build)
to help you with the calculation

Good luck

--
Wayne
Manchester, England.



:

I am creating an Access database and I would like a field in a table
to have
a default that automatically gives the numerical sum of two other
fields in
the same table, BUT also maintain the ability to edit the field. I
would
like the field to just give the sum as a default, not a fixed value.
Example: Field 1 is "A Amount" and Field 2 is "B Amount" and I would
like the
sum of "A Amount" and "B Amount" to show up in Field 3, but be
editable/overwritable. It would not work for me to put this
expression into
the Default Value for Field 3 in the table. Is this possible?
Did this post answer the question?

 

[UpRider]

Ryan, without making a judgment about the wisdom of your design, here's how
to do it.
Remove any code to update field3 that you have now.

Private Sub field1_AfterUpdate()
field3 = field1 + Nz(field2, 0)
End Sub

Private Sub field2_AfterUpdate()
field3 = field2 + Nz(field1, 0)
End Sub

With this, any time field1 or field2 is updated, the update is reflected in
field3. If field3 is bound to a field in your table or query, it will be
stored in the table. You can make a different entry in field3 any time, but
updating field1 or field2 will override the manual entry.

UpRider