Populating table cells with a calculation, not data entry

[GregP]

I know this is possible in forms (Row source = [Field1] * [Field2]), but I've found no way of actually linking this calculated result to a table cell. Even better would be to do this calculation at table level so any forms or queries based on the table would have access to the calculated information. Any ideas?

 

[Rich]

You wouldn't normaly store calculated results, if you want to use the values elsewhere then use a query to return the calculation

 

[GregP]

Well in this case I do want to store calculated results. I have two fields, [Cost] and [Freight], from which I want to calculate [Profit] (i.e. [Profit] = [Cost] - [Freight]). I then want to be able to use this stored value elsewhere in the database, e.g. in reports and forms. So a table is really where I want the info, but you suggested using a query, could you please explain what you had in mind?

 

[KevinM]

And if cost or freight values were to change then ALL your previous 'stored' values would be incorrectly calculated, where as if it were done in a query (as rightly suggested), then calculation would always be correct.

 

[GregP]

OK, let's go with the query idea, I'm not too fussed either way. How would I do it in a query? I go into design view, can then select which fields I want and in what order, but I still don't see how that helps me calculate a result for one of the fields.

I've got no problem at all doing it in a form, creating an unbound field and calculating the data from that, but I want the calculated result stored in a bound field so I can view it in a table or query. But in order to calculate within a form field, it appears necessary to delete the control source which binds the field to the table and replace it with the appropriate formula (profit = cost-freight), thereby breaking the link which would previously have allowed that result to be stored in the table.

So if the query method will work, great, please step me through it.

 

[neileg]

In the QBE grid, add a new field with this in the field line
Profit:Nz([Cost]) -Nz([Freight])

The Nz ensures that null values don't invalidate the maths by converting them to zero.

 

[GregP]

Well it half worked. It allowed me to enter the line you suggested, and accepted it with no errors (which is quite an achievement for most of the code I've entered thus far!), but it didn't produce a result - the field was blank when I went into datasheet view. I ensured the fields 'Cost' and 'Freight' were included in the query - also tried tblOrders.Cost and tblOrders.Freight just in case it got confused, but didn't fix it. The SQL looked fine too - Nz([PricePaid])-Nz([DeliveryPaid]) AS Profit. But no figures. Any ideas?

 

[neileg]

Erm, are you trying to confuse me? You talk about Cost and Freight and then your SQL shows PricePaid and DeliveryPaid.

Assuming these are the correct field names, are you sure that these have values in them? Try running a query that just has three fields, the Cost, the Freight and the calculated profit field. If you have values for the first two, you should get the profit.

You shouldn't just chuck tables into your query, there has to be a good reason for them being there. Access doesn't get confused, but it does do what you tell it to do, which is not always what you want it to do!

 

[GregP]

Sorry about the field names, I said Cost and Freight to simplify it, but PricePaid and DeliveryPaid are actually the field names I'm using.

Well that worked. I made the new query as you suggested, and it went fine. I then replicated it in my original query, and it worked again. Not quite sure why it didn't before, but it does now which is the main thing. Thanks a lot for your help!