C
Craig
How would one go about creating a column in a table that
is a mathematical equation of two other columns in the
same table?
is a mathematical equation of two other columns in the
same table?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
Craig
OK, I have figured out HOW it is done, just not CORRECTLY.-----Original Message-----
How would one go about creating a column in a table that
is a mathematical equation of two other columns in the
same table?
.
Now it is telling me my syntax is incorrect. Can someone
please explain what syntax I need to use for a basic
addition function?
T
Tim Ferguson
How would one go about creating a column in a table that
is a mathematical equation of two other columns in the
same table?
One wouldn't. One calculates stuff either in a form or report or in a
query.
Tim F
G
Guest
Thank you, just one more question. Why then, does it give-----Original Message-----
One wouldn't. One calculates stuff either in a form or report or in a
query.
Tim F
.
a space for a default value with mathematical operations
as options?
A
Anne Nolan
Thank you, just one more question. Why then, does it give
a space for a default value with mathematical operations
as options?
Default Values are only used when a new record is created. They are not
involved in subsequent updates to records. You can, I guess, have a
complicated calculation for a default value, but it'll only fire when
creating the record.
Tim is right; you use queries to display the results of calculations.
Tables are for data, not formulas
Anne
G
Guest
Even nmore important. Where in the form builder do I enter-----Original Message-----
One wouldn't. One calculates stuff either in a form or report or in a
query.
Tim F
.
the expression that I want to use for calculation?
C
Craig
Let me explain a little further. I am creating a simple-----Original Message-----
Thank you, just one more question. Why then, does it give
a space for a default value with mathematical operations
as options?
.
one table database to track orders I have made. I have 10
item entries per database row. I will be entering quantity
and price for each item. I also have a space for total
dollars for each item. After I enter quantity and price I
want it to calculate total dollars. this is a very simple
function. Logic tells me that it could be done at the
table level, or on the form itself. After I figure this
out, I will probably also have a page total, adding up all
of the totals from before.
Where is the best place to do these calculations?
K
Kelvin
Craig, like the others have said you cannot perform math at the table level.
Since you are performing the same equation on each field there is no need to
store the results of the calculation. Its best if you leave the table as is
without the field for the total. Then using the query builder create a
query using every field from this table. Manually add another column to the
query that is generated with the equation you want. Then everytime you run
the query the result will be shown. You can use this query to generate your
reports.
If you insist on storing the result of the calculation, here are 2 options
(although there are others).
1) Create a form of this table. Add a command button to you form. In the
OnClick event of the button add the following code.
Me.txtTotal = ... 'Insert your equation here. Change txtTotal to the
correct name.
2) Create an update query that will update the total column of the table to
the result of your calculation.
Kelvin
Since you are performing the same equation on each field there is no need to
store the results of the calculation. Its best if you leave the table as is
without the field for the total. Then using the query builder create a
query using every field from this table. Manually add another column to the
query that is generated with the equation you want. Then everytime you run
the query the result will be shown. You can use this query to generate your
reports.
If you insist on storing the result of the calculation, here are 2 options
(although there are others).
1) Create a form of this table. Add a command button to you form. In the
OnClick event of the button add the following code.
Me.txtTotal = ... 'Insert your equation here. Change txtTotal to the
correct name.
2) Create an update query that will update the total column of the table to
the result of your calculation.
Kelvin
A
Adrian Jansen
Study the Northwind database example that ships with Access, to see examples
on how to set up your tables correctly, and how to do calculations on forms.
--
Regards,
Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
on how to set up your tables correctly, and how to do calculations on forms.
--
Regards,
Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
J
Jeff Boyce
Craig
From your description of "10 item entries ... per row", including quantity
and price, you have described a ... spreadsheet! Access is a relational
database. Your design might have been necessary for working in Excel, but
is counterproductive for working in Access.
Consider reviewing the topic of normalization for ways to improve your basic
data structure -- doing so will allow you to make much better use of Access'
strengths.
You might ask "why not do it my way?" What will you do when the number of
items changes? Change your table structure, change your forms, change your
queries, change your reports, change your code modules?!
On the other hand, if your table structure was more like that reflected in
the Northwind database for Order and OrderItem, you could have zero, one or
a huge number of "items" without having to make a single change in any of
the objects mentioned above.
From your description of "10 item entries ... per row", including quantity
and price, you have described a ... spreadsheet! Access is a relational
database. Your design might have been necessary for working in Excel, but
is counterproductive for working in Access.
Consider reviewing the topic of normalization for ways to improve your basic
data structure -- doing so will allow you to make much better use of Access'
strengths.
You might ask "why not do it my way?" What will you do when the number of
items changes? Change your table structure, change your forms, change your
queries, change your reports, change your code modules?!
On the other hand, if your table structure was more like that reflected in
the Northwind database for Order and OrderItem, you could have zero, one or
a huge number of "items" without having to make a single change in any of
the objects mentioned above.
G
Guest
On the other hand, if your table structure was more like
that reflected in
Jeff-
This example is exactly what I want. Let's look at the
Northwind example. They have 2 forms, orders and customer
orders. They both do a price/quantity/extended price
calculation that is what I want to do. Where is that
calculation performed?
that reflected in
the Northwind database for Order and OrderItem, you could have zero, one or
a huge number of "items" without having to make a single change in any of
the objects mentioned above.
Jeff-
This example is exactly what I want. Let's look at the
Northwind example. They have 2 forms, orders and customer
orders. They both do a price/quantity/extended price
calculation that is what I want to do. Where is that
calculation performed?
J
Jeff Boyce
I've not dug "behind the curtain" recently, so I'm only guessing...
A calculation of "extended price" is probably derived from the quantity
times unit price. It can be done in a query or in code behind the form.
It isn't necessary to store that calculation, as the quantity and/or unit
price might change.
Since you're describing the use of a form, I'd probably put the calculation
in code behind the form. Perhaps a general function that fills an unbound
textbox with the results of the multiplication. And a line of code in the
AfterUpdate event of each of the two related fields (quantity and unit
price). That line of code would look something like (actual syntax may
vary):
Call MyExtendedPriceCalculationFunction()
A calculation of "extended price" is probably derived from the quantity
times unit price. It can be done in a query or in code behind the form.
It isn't necessary to store that calculation, as the quantity and/or unit
price might change.
Since you're describing the use of a form, I'd probably put the calculation
in code behind the form. Perhaps a general function that fills an unbound
textbox with the results of the multiplication. And a line of code in the
AfterUpdate event of each of the two related fields (quantity and unit
price). That line of code would look something like (actual syntax may
vary):
Call MyExtendedPriceCalculationFunction()
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.