Make Table Query to Include A Calculated Field

[jrub]

Hi All,

Is it possible to program a make table query to include a calculated filed in the final table? By include, I mean include the calculation, not the static value?

For example, I have a table with Rate, Interest and Total and other data.

I would like the make table query to make the table with the other data, but also include the formula Rate*Interest, giving rise to the Total Field.

Any way to do this easily?

 

[Beetle]

The best way to do this is not to do it at all. In most cases, calculated values like this should not be stored in a table. Even if you're using A2010, which gives you the ability to create a calculated field in a table, I do not recommend it. These calculated table fields are unreliable, incompatible with older versions of Access, an offer no benefit whatsoever over placing the calculated field in a query where it belongs.

There are a few exceptions to that general rule of course, but at first glance it doesn't appear that this is one of them. Is there a reason why you believe this calculated value needs to be stored in the table?

 

[jrub]

Well, I am trying to put together a way to change the interest rates, and have the balance automatically adjust to match other parameters. At first, it may sound easier in excel, though due to the fact the other parameters are summations of a large amount of data needing access to calculate, I thought I would be able to the analysis part going in Access.

Essentially, I could get around by using an update query. Once a percent changes, I could run the update query to update the values to reflect the new interest. Unfortunately, I then require the Balance to be updated, and it sums the previous years balance and interest.

So I am stuck trying to figure out how to get access to sum the previous record's values via a query.

For example, I have a table with all the base data, and Im trying to buidl a query to update the balance with this command:

RunningBalance2: CCur(Nz((SELECT Sum(Nz(RunningBalance,0)+T1.AnnualContribution+T1.InterestIncome+T1.Inflation_Adjusted_Expenditures) FROM FP_Default As T1 WHERE T1.StudyYears<FP_Default.[StudyYears]),[ReserveParameters]![StartingBalance]))

But this adds the new interest and contributions to the old, orginal base data, and not the new calculated in the query.

I am at a complete loss.....so over my head.

 

[jrub]

I think this has morphed into another thread, which I started below:

http://www.access-programmers.co.uk/forums/showthread.php?t=242008