By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Sum a column of values from a query in a form field

P: n/a
MX1
I have a query written in MS Access that has a few calculated fields. Is it
possible to refer to that query in a form field. I'd like the form field to
show the sum of one of the columns from the query I've written. I've tried
to put the following in the form field's control source property but I'm
getting an error. Query1 is the name of the query and CalculatedTotal is
the calc'd field column I'm trying to get a total for in the form field.

=Sum([Query1]!CalculatedTotal)

Many Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
MX1
Thanks Rick. I had never seen DSUM used in that context before. It worked
like a charm. You're awesome!

Thanks again!
"Rick Brandt" <RB*****@Hunter.Com> wrote in message
news:bj************@ID-98015.news.uni-berlin.de...
"MX1" <mx*@mx1.abc> wrote in message
news:YV*********************@rwcrnsc51.ops.asp.att .net...
I have a query written in MS Access that has a few calculated fields. Is it possible to refer to that query in a form field. I'd like the form field to show the sum of one of the columns from the query I've written. I've tried to put the following in the form field's control source property but I'm
getting an error. Query1 is the name of the query and CalculatedTotal is the calc'd field column I'm trying to get a total for in the form field.

=Sum([Query1]!CalculatedTotal)
The standard aggregate functions only work on the current bound RecordSet

and the aggregation is based on where they are used (Group Header/Footer, Form Header/Footer, etc.). In your case Sum([CalculatedTotal) would work if the form was actually bound to the query [Query1] and you placed the control in either the Form Header or Form Footer.

If your form is bound to a different data source and you just want to display this sum on your form then you need to use a Domain Aggregate function as these provide an argument for the DataSource to look at. In your case...

=DSum("[CalculatedTotal]", "[Query1]")

...should work. Domain Aggregate function have no concern about their placement either. You could place the control anywhere you like on your form and it will return the same result.

Nov 12 '05 #2

P: n/a
MX1
One more question, I'd like to make the new calcualted form field only add
records associated with the client currently selected on the form. As I
scroll through the clients, I'd like the DSUM to change accordingly. I'm
wondering if I should filter at the original query or the fields on the
form? Any thoughts?
"MX1" <mx*@mx1.abc> wrote in message
news:7m*******************@rwcrnsc52.ops.asp.att.n et...
Thanks Rick. I had never seen DSUM used in that context before. It worked like a charm. You're awesome!

Thanks again!
"Rick Brandt" <RB*****@Hunter.Com> wrote in message
news:bj************@ID-98015.news.uni-berlin.de...
"MX1" <mx*@mx1.abc> wrote in message
news:YV*********************@rwcrnsc51.ops.asp.att .net...
I have a query written in MS Access that has a few calculated fields. Is it possible to refer to that query in a form field. I'd like the form field to show the sum of one of the columns from the query I've written. I've tried to put the following in the form field's control source property but I'm getting an error. Query1 is the name of the query and CalculatedTotal is the calc'd field column I'm trying to get a total for in the form field.
=Sum([Query1]!CalculatedTotal)
The standard aggregate functions only work on the current bound

RecordSet and the
aggregation is based on where they are used (Group Header/Footer, Form Header/Footer,
etc.). In your case Sum([CalculatedTotal) would work if the form was

actually bound
to the query [Query1] and you placed the control in either the Form

Header or Form
Footer.

If your form is bound to a different data source and you just want to display this
sum on your form then you need to use a Domain Aggregate function as

these provide an
argument for the DataSource to look at. In your case...

=DSum("[CalculatedTotal]", "[Query1]")

...should work. Domain Aggregate function have no concern about their placement
either. You could place the control anywhere you like on your form and

it will
return the same result.


Nov 12 '05 #3

P: n/a
MX1
All set, I filtered by ClientID on the dsum in the form and it worked. The
problem was that I didn't have ClientID selected as a field in the original
query. Thanks again!
"MX1" <mx*@mx1.abc> wrote in message
news:lB*********************@rwcrnsc51.ops.asp.att .net...
One more question, I'd like to make the new calcualted form field only add
records associated with the client currently selected on the form. As I
scroll through the clients, I'd like the DSUM to change accordingly. I'm
wondering if I should filter at the original query or the fields on the
form? Any thoughts?
"MX1" <mx*@mx1.abc> wrote in message
news:7m*******************@rwcrnsc52.ops.asp.att.n et...
Thanks Rick. I had never seen DSUM used in that context before. It worked
like a charm. You're awesome!

Thanks again!
"Rick Brandt" <RB*****@Hunter.Com> wrote in message
news:bj************@ID-98015.news.uni-berlin.de...
"MX1" <mx*@mx1.abc> wrote in message
news:YV*********************@rwcrnsc51.ops.asp.att .net...
> I have a query written in MS Access that has a few calculated
fields.
Is it
> possible to refer to that query in a form field. I'd like the form

field to
> show the sum of one of the columns from the query I've written.
I've tried
> to put the following in the form field's control source property but I'm > getting an error. Query1 is the name of the query and
CalculatedTotal is
> the calc'd field column I'm trying to get a total for in the form

field. >
> =Sum([Query1]!CalculatedTotal)

The standard aggregate functions only work on the current bound

RecordSet
and the
aggregation is based on where they are used (Group Header/Footer, Form

Header/Footer,
etc.). In your case Sum([CalculatedTotal) would work if the form was

actually bound
to the query [Query1] and you placed the control in either the Form

Header
or Form
Footer.

If your form is bound to a different data source and you just want to

display this
sum on your form then you need to use a Domain Aggregate function as

these
provide an
argument for the DataSource to look at. In your case...

=DSum("[CalculatedTotal]", "[Query1]")

...should work. Domain Aggregate function have no concern about their

placement
either. You could place the control anywhere you like on your form

and it
will
return the same result.



Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.