Calculating a sum in a query

[Paras Shah]

Need some help to calculate a sum in a query for a group of records. Please
see an example of 5 different rows in my query:

Project No. Industry Value
12 Software 6
12 Software 7
12 Software 8
16 Hardware 4
16 Hardware 8

So, what I want is to sum all the "values" for a given "Project No" -
essentially I want the resulting query to look like

Project No. Industry Summed Value
12 Software 21
16 Hardware 12

And then I need to run other calculations on "Summed Value"

Any help in getting to a "Summed Value" field/ expression is most
appreciated.

-Paras

 

[Steve Schapel]

Paras,

Make a query in design view, based on your table and including the 3
fields shown in your example. Make it into a Totals Query (select
Totals from the View menu). In the Totals row of the query design grid,
leave Group By in the first 2 columns, and enter Sum in the Value
column. Run the query.

 

[Tom Wickerath]

Hi Paras,

You need to use a totals query. Open your existing query in design view. Click on the "Totals"
toolbar button (or click on View > Totals). You should see a new row in the QBE (Query By
Example) grid labeled "Total:". The default value is "Group By". For the Value column only,
change "Group By" to "Sum". It's as easy as that!

Note: If you view the SQL statement before and after adding the totals, this is what you will
see. I used tblProjects as the name of the table, and ProjectNo (no spaces) as the name of the
first field. To view the SQL statement, click on View > SQL in query design view.

Original Query:

SELECT tblProjects.ProjectNo, tblProjects.Industry, tblProjects.Value
FROM tblProjects;


Query with Value field totaled:

SELECT tblProjects.ProjectNo, tblProjects.Industry, Sum(tblProjects.Value)
AS SumOfValue
FROM tblProjects
GROUP BY tblProjects.ProjectNo, tblProjects.Industry;

Incidentally, I suggest using a different field name instead of "Value". Value (and Values) are
reserved words in Microsoft Access:

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=209187


Tom
_____________________________________


Need some help to calculate a sum in a query for a group of records. Please
see an example of 5 different rows in my query:

Project No. Industry Value
12 Software 6
12 Software 7
12 Software 8
16 Hardware 4
16 Hardware 8

So, what I want is to sum all the "values" for a given "Project No" -
essentially I want the resulting query to look like

Project No. Industry Summed Value
12 Software 21
16 Hardware 12

And then I need to run other calculations on "Summed Value"

Any help in getting to a "Summed Value" field/ expression is most
appreciated.

-Paras