Totaling a field in a Query - Easy Right? No, not for me.

[chablups]

I have searched the archives and can find answers to much more complicated questions than mine, but not my question. Mine, I think, is embarrassingly easy to solve - - I am trying to make a query that will total up the total value of a list of homes that have sold. So, like, there are ten homes that sold and their sale prices are listed. In the Query when I try to total them up in the search criteria by putting =Sum([Sale Price]) I keep getting this message - Cannot have aggregate function in WHERE clause. What the heck is a WHERE clause? Is it not possible to get a total in a query? I know I can make a report from a query and then total it there, but I want to eliminate that step if possible. Is it possible? If so, please, tell me how. Thanks.

 

[Rich]

Since you've already done it on a report you can also use the same method on a form. If for some reason you need a query to give you the result the you need a separate Totals query, based on the first.

 

[chablups]

Thanks Rich. So it sounds like you can not total up a row of numbers in an initial Query. You have to make a second query based on the first. If that is the case then you might as well just total it up in a Report. The reason I want to find the total in the first query is so I don't have to take the time of the second step, of making a report. Does that make sense? Why can't you total a row of numbers up in a query? That seems strange that you wouldn't be able to do that.

 

[chablups]

How do I get to the QBE grid (don't know what it is)? And what is the Sigma Icon, how do I recognize it? (Sorry, I have only been using access for a couple weeks, pretty much 24/7 though.)

 

[ejstefl]

The QBE grid is what you see when you enter a query in design mode. The sigma button is on top, and looks sort of like an E. Its immediatley to the left of the drop down box that says all. Just hit this button, and down below you will see a new "row" that says "Group by." Change it to sum and run the query.

 

[chablups]

Thank you ejstefl for you explicit instuctions, but I still can't get it. I found the QBE grid, and the Sigma Icon and did EXACTLY what both you and Pat said to do, but I when I run the query, (I assume that means hitting the little ! point at the top that says Run when you highlight it)I still get no totals. The only change I notice when I run the query is that the last blank line that is there when I preview the query is gone. What am I doing wrong?? What am I missing. I've been trying to get this for last couple days. Thanks!

 

[chablups]

Okay, I think I have discovered the answer. I think Rich had it right. Once you are in your QBE Grid you have to then right click in the upper protion of the screen and get yourself a "Select Query", then you drop in the fields you want to total. THEN you hit the Sigma Icon, Change the Group by to Sum and run the Query. That worked, but for my purposes it will be quicker to just make a report from my original query and set the totals there. If there is an easier way, please let me know. I have lots of Queries and would like to avoid having to make reports for each one of them to get totals if possible. Thanks all!

 

[Rich]

If you only want to view the summary data without having to run a report each time create another form based on the original query, in the form header just put textboxes for the fields you want to Sum ie. =Sum([Amount])
remove the navigation buttons etc.
HTH