Can you do calculations in a table? 1

[mrtauntaun]

I have two fields in a table and I would like to multiply them in field three. Is this possible? I can do it in the form, but it would make my life easier if I could put it in the table.

 

[koresnordic]

Create a udpate query that multiples the two numbers together. You can put conditions in if you wish

Graham

 

[koresnordic]

If you wish me to e-mail you a small databse that does just this, then leave your e-mail address.

Graham

 

[evalesthy]

Why don't you do this in a query and then base the form (or report) on the query. You really don't want to store these values in your table. In your query your first column would be Field1 (whatever your field names are), second column Field2, and third column "Calculation: [Field1] * [Field2]" (with quotes removed). This is quite straightforward unless there is some other issue we are not aware of. You could also (as you mentioned) just use a calculated control on your form - but you had some reason for not doing this.

 

[mrtauntaun]

Well, I need to get this field summed up in another query, and then have the original field along with the sum appear on the same report. I've tried it this way, but I keep getting a circular logic error.

 

[JeremyNYC]

There are very few cases in which you'll want to store a calculated value in a query. If you're having trouble there are a few things you can do. The simplest is to create one query that does the first calculation and a second query that is based on the first query that does the second calculation.

Another thing that doesn't occur to some people is that you can refer to the original fields in several fields in your query. Instead of doing this:
SELECT Qty, Price, (Qty * Price) as SubTot, (SubTot * 1.08) as Tot FROM tblSales

(which may or may not work), try this:

SELECT Qty, Price, (Qty * Price) as SubTot, ((Qty * Price) * 1.08) as Tot FROM tblSales

Will something like that work for you?

Jeremy =============
Jeremy Wallace AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

See the Developers' section for some helpful fundamentals.

See thread181-473997 for information on how to get the best responses possible here at Tek-Tips.

 

[mrtauntaun]

Hmmm, that may do it. I will try it when I get to work tomorrow. Thanx!