Link to home
Create AccountLog in
Avatar of jlarger
jlarger

asked on

How do I perform calculations with totals?

I have a form that has several fields, as well as additional subforms. The subforms are displayed in datasheet view and everything is related by a Client ID. I am trying to find the total in column D, and then work with that number to perform several calculations. I have it where I can get the total, but I have no idea how to reference that value so I can perform the calculations.

Sorry this isn't more detailed, I am trying to not confuse myself or anyone else.

Thanks
Avatar of als315
als315
Flag of Russian Federation image

Field with Total value have name, you can use it in calculations.
For example, filed name is MyTotals, you can calculate other field (15%) assigning it to
Me.MyTotals*0.15
If value from subform must be used on main form, I can recommend you create dummy query and use query builder for proper syntax
Avatar of jlarger
jlarger

ASKER

Wizard:
I'm not sure if I understand your suggestion correctly, but I think I do. As it stands the total is not its own field/column. The column name is Refund Checks. I want to total the refund checks (with respect to the current Client ID) and then work with that total.
Avatar of jlarger

ASKER

Sorry als315, I was looking at your rank not your name...

How would a query like that look?
Are you adding Totals in Form footer?
1. Does this "Have" to be in a form...?
This can be done quite easily in a Report...


2. "The subforms are displayed in datasheet view"
This would be much easier if this was a "Continuous" (tabular) form view, which can have the same look as datasheet view, but would be much simpler to display and Manipulate your totals.

Sample attached





Database50.mdb
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You can add a calculated field to you query, like

MySum: Sum([MyField])

Then add this field to the form and hide it in datasheet view.

In the main form, you can refer to the field with

=MySubform.Form!MySum

where MySubform is the name of the subform control and MySum  the name of the control you just put in the subform.

Also, you can use a aggregate domain function

=Dsum("MyField","MyTable","ClientID=" & cliendID )

Avatar of jlarger

ASKER

Wow guys thanks. I didn't even know continuous forms  existed (it's been awhile since using access).
Going to be implementing a combination of boaq and gilbertoM's advice.

Boaq - yes it does have to be in a form and not a report, but did not HAVE to be datasheet as you pointed out, continuous forms is just as if not more functional. Bonus props for the great example you provided.

Thank you both. I'll post as answered as soon as I implement the solution and get it functional.
Great,

It is always best to know more than one way of doing something...

;-)

JeffCoachman