Calculating in query and using it in form

Question

I have a sub-form with six calculation 

1.Quanity*Rate=AccessAmount
2.AccessAmount -Discount=TaxableValue
3.TaxableValue*Tax1%=Tax 1 Amount
4.TaxableValue*Tax2%=Tax 2 Amount
5.TaxableValue*Tax3%=Tax 3 Amount
6.SubTotal=TaxableValue + Tax1 Amount + Tax2 Amount + Tax3 Amount

Now I want to calculate the total of all the Column Total value in Sub-form footer how. If it requires VBA then i will switch back to excel but if possible without VBA it would be great as I like access form entry more than excel.Thank in advance

Answer 1

Hi HKiran,

I try to create a table based on your description.

Then I create a query to calculate data.

SELECT 
qty*rate as AccessAmount,
AccessAmount-discount as TaxableValue,
TaxableValue*1/100 as Tax_1_Amount,
TaxableValue*2/100 as Tax_2_Amount,
TaxableValue*3/100 as Tax_3_Amount,
TaxableValue + Tax_1_Amount + Tax_2_Amount + Tax_3_Amount as SubTotal
 from product_data;

Output:

Then I try to create a Form. Now I have a confusion that your form shows data in Datasheet view or in Form View?

If you are displaying in Datasheet view then you can add Total like below.

You can add Totals like above from 'Totals' option in Home Tab.

If you are displaying the form in Form View like below.

Can you confirm, Which Total you want to display in textbox for Total?

If possible you can show us the picture of your desired output.

We will try to provide you suggestions to achieve that.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].