Calculation from 2 tables?

[kbrooks]

I'm trying to create a field that is a calculation based on 2 different fields. It's account_balance times percentage divided by 100. My problem is that account_balance is in the AccountData table, and percentage is in ApplicantData table. Any way to do this?

I currently have in my query:
fap_payment:[account_balance]*[percentage]/100
but when I run it, it prompts me for the percentage

I also tried:
fap_payment:[account_balance]*[ApplicantData]![percentage]/100
but it still prompts me when I run it.

Thanks in advance!

 

[Tiro Cupidus]

The only thing I would know to do is check the spelling of the the percentage field in the table and the reference to it in the query and make sure they're exactly the same. Maybe even retype both of them to be sure.

 

[kbrooks]

Yep, they are exactly the same and I still get prompted to enter a field. What is the correct way to type it?

fap_payment:[account_balance]*[percentage]/100
or
fap_payment:[account_balance]*[ApplicantData]![percentage]/100
or something entirely different?

The field account_balance is not actually in a table, it's the result of another calculation and is created in this query. But they both worked before when I had everything all in one table. It's just now that I split them into 2 tables that I have problems.

 

[ColinEssex]

You could try the round brackets like this

([account_balance]*[percentage])/100

Col

 

[kbrooks]

Didn't work.

 

[Rich]

Post the SQL from your query

 

[kbrooks]

Here it is. I'm sure how I have the last field is probably wrong.

SELECT AccountData.ss, AccountData.account, AccountData.date_service, AccountData.total_charges, AccountData.[3rd_party_payment], [total_charges]-[3rd_party_payment] AS account_balance, [account_balance]*[ApplicantData]![percentage]/100 AS fap_payment
FROM AccountData;

 

[Rich]

What's the table name that contains percentage, I don't see it in your statement, have you added it to your query grid?

 

[kbrooks]

Well just slap me now.

I didn't have relationships built between the 2 tables AND I didn't have it included on the query grid.
Thanks for all your help, I believe it's working now!!