473,554 Members | 2,334 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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]!CalculatedTota l)

Many Thanks.
Nov 12 '05 #1
3 29003
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******** *************@r wcrnsc51.ops.as p.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]!CalculatedTota l)
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
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******** ***********@rwc rnsc52.ops.asp. att.net...
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******** *************@r wcrnsc51.ops.as p.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]!CalculatedTota l)
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
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******** *************@r wcrnsc51.ops.as p.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******** ***********@rwc rnsc52.ops.asp. att.net...
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******** *************@r wcrnsc51.ops.as p.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]!CalculatedTota l)

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2977
by: mike | last post by:
Ok, I have a page that queries a database and retrieves values to create a custom report in ms excel format on the fly so that it can be downloaded. I can use the following sub to generate the report just fine. It works correctly for every field in the database and creates the report as desired except for one field and one field only. The...
20
2740
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into normalized data. The task is made more difficult by the fact that the structure itself can vary from month to month (in well defined ways). So,...
1
2289
by: Steve | last post by:
I have looked through the newsgroup for an answer to this but haven't been able to find anything resembling my situation. What I want to do is relatively simple, I think. I have a crosstab query that is bound to a report I am creating. I would like the column headings to be the name of the correspoding field name. For example, I have...
3
3146
by: ssb | last post by:
Hello, This may be very elementary, but, need help because I am new to access programming. (1) Say, I have a column EMPLOYEE_NAME. How do I fetch (maybe, cursor ?) the values one by one and populate a combo box with these names. (this way, I can display all the EMPLOYEE_NAME values) (2) In general, can I do additional processing on...
13
4160
by: nyt | last post by:
I have a problem of number and text field. I got the database file(mdb) that contains many combo boxes used and its list values are created by "value list" For eg field Field name= 'furniture' , data type='Number' ,Display Control='Combo Box', RowSource Type = 'Value List' and Row Source = ' 0;"chair";1;"Table";2;"Bed" ' Therefore, in...
6
2413
by: Steve | last post by:
I realize that this probably isn't a best practice, but I'm working with legacy code that has a query stored in one column of a table. Because the queries vary, the JSP page that selects a query and runs it cannot add any additional information (like a WHERE clause). I need to add a few more records to the the table, and would like the...
1
3497
by: Jimmy Stewart | last post by:
I have a continuous form has an unbound field named "Client". Its controlsource is " =.(1) " This allows me to display the client name without the combo box dropdown arrow. I want users to be able to sort the records by the client name but I am unable to figure out how to reference this in my code. If I used the following code, I can sort...
2
6540
by: Exick | last post by:
This is more of a minor annoyance/curiosity than a real problem, but I'm wondering if anyone here can provide some answers. I have a form bound to a table with lots of controls on it that are bound to fields of said table. I also have a combo box on the form that is unbound. I'm using it purely as a search mechanism. Anyway, the combo box...
0
2126
by: Clare CAVS | last post by:
I have a table with a lookup column referring to another table . tblRooms has two fields, (Autonumber), and . The column I want to display is the RoomName column. If I have Bound Column = 1, and Column Count = 2, Column Widths 0cm;3cm I get the display I want. However, if I change the Bound Column to 2, this changes the values displayed...
0
7612
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7536
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7814
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7576
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7895
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5165
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3583
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1147
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
853
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.