Calculation within a table? (1 Viewer)

[kbrooks]

Is it possible to create fields in the table that are based on calculations from other fields, instead of on user input?

I have a table AccountData with several fields, including
total_charges and 3rd_party_payment

I want to create a field account_balance that would be the total of total_charges minus 3rd_party_payment. I know I could do that on the form/query/report, but I'm wondering if it's possible to do it at the table level.

I also want to create another field that is based on the above one. I would create fap_payment that would take the above created account_balance and multiply it by a field called percentage in a separate table (ApplicantData).

Is this possible? I'm running into problems with every way I've tried it. Thanks in advance!

 

[Sohaila Taravati]

No, you can not do it in the table but you can in a query.

 

[kbrooks]

I can't get it to work in the query....it DID when I had everything in one table, but now that they're split into two I can't get it to calculate.

 

[Sohaila Taravati]

I will give an example. Let's say you have two fields in your table called [Price] and another called [Tax]. In your query you can do a claculation like this:
TotalTax:[Price]*[Tax]
If your price is $15 and there is %8 tax then the [TotalTax] will be $16.20.
Pretty much any calculation can be done in a query and you don't have to have a seporate field for each calculation. What I mean is you don't have to have the [TotalTax] field in your table in order for you to do your calculation. I hope I this makes sens

 

[kbrooks]

Yes, it does make perfect sense. But can you still do this if the fields in the calculation are in 2 different tables?

account_balance is a field created in a query using total_charges and 3rd_party_payments in the AccountData table. That one is no problem.

However, I'm running into problems when trying to create a field called fap_payment. It is the total to the account_balance field (created above in the query) multiplied by the field "percentage" in a whole other table...the ApplicantData table.

I hope I'm explaining myself correctly...

 

[Sohaila Taravati]

What is commen between your tables? You should be able to link the tables in your query and making sure the joint properties are set correctly. Then grouping your data. Since I don't have your database in front of me, I can't really see the problem. I am just gussing!

 

[Rich]

Why not stick to the other thread you have on the problem?

 

[kbrooks]

Wasn't sure if it was a query or a table problem...posted in both to be sure.