Calculated field in access

[Pettor]

Hi All,

I have created a form in access through which I enter data to a table.

I have created also some fields in the form that they do some calculations and I want to enter these calculations to the table and I want each time I go back and forth to the records through the form to see the calculations that took place in the first place.

Is it possible?

 

[Marbles]

A better way, would be to create a Query based on the Table. Do the calculations there. Base the form on the Query. The calculations can show on the Form.

 

[Pettor]

The table that contains the data for the calculations is different than the one that is updated through the form. Would it be possible to combine both inside the form?

 

[Micron]

The general rule of thumb is that it's bad practice to store calculations. Not only is it not necessary 99.5% of the time (figuratively speaking) it can be somewhat problematic. If you have value A and value B you can calculate C on a form without fear that should you or anyone alter A or B, the value you stored becomes wrong. Imagine storing the total cost of an invoice then realizing the quantity was 10, not 100. You charge for 100 because you stored a calculation?

If A and B come from different tables, you base your form on a query, not a table.

 

[Pettor]

I am afraid that my case belongs to this 0,5%, so let me describe the scope a little bit, to tell me if I am missing something here.

I use the form to update a table (A) with some actions that have to be taken, which are based (not strictly, mostly indicative) on these calculations.

The data of the calculations are stored on a different table (B).

So when the result is lets say a random number, i.e. 10, I want to take an action that will be stored in the table A through the form.

What I want to know, in a future time, is which results triggered this specific action in the first place and I can do it only (I believe) if I can store also the result of the calculation in the same record.

 

[xenou]

You could probably use code in the form to update/save a record based on whatever is in the form. The use case scenario isn't quite clear - if the data is in the form then it's also in the table (?) so you could just use sql updates too (with or without the form even being open ????). Not entirely clear. In any case, the answer is "Yes, it is possible" but I don't think you've given enough information to be more specific.

 

[Micron]

That's what I was going to say when I got to it - not enough info. Except I can speculate that if one table holds values and I have to take action when the calculation using those values meets or exceeds 10, then it is fair to assume I can do that in a form, report or query as already mentioned. Assuming further that the calculation is a sum, then I don't take action when the DSum of the records is less than 10. Given that or any similar scenario, there is no need to store the calculation.

So far, you've had 3 responders suggest that you not store the calculations. Should you be determined to do so, then it is quite possible, but as noted, there's not enough information to suggest how. Perhaps the values are input along with the some relevant identifier, such as a job number. You might store the value in another table against the job number. You would have one record related to perhaps many records for that job number, assuming each job could have several records, each one holding a value that needs to be part of a calculation. Should you have multi valued fields for these values, then I'd say you're really asking for trouble. I wouldn't even want to suggest how to incorporate them.

 

[Pettor]

Hi,

Sorry for my late reply. Let me describe it a little further.

First clarification is that the calculated data are not in the destination table (that is what I am trying to do after all).

I want to make a bet tracking table (through the form) gathering my choices. These choices of course are based (mostly but not always) on some data which I would like to see in the entry form so as to have a clearer view when I make the selection.

So the calculated data are something like Home win 50%, Away win 50% end so on (which are stored in a Results table separately - The whole data not the calculation result). These are now calculated in the form with a txt field and I would like to enter these calculations to the final table so as to be able to understand at a later stage if these indicators were correct.

Let me know if you need more details.

Thanks

 

[xenou]

Hi,
The answer is still, yes it's possible It doesn't even seem like a very difficult thing to do, actually - this is after all what databases are for (storing data -- with the caveat that large relational databases work best with properly designed table structure). Which makes me reluctant to go through a detailed explanation of a generalized and common database action. Have you tried anything at all yet?