# NZ Function on Sum Total

Views: 74
I have a "hour" field that I am summing, in the query, for a report. There are quite a few null values that I need to insert a "0" for. Also I need values less than 6, but when I put

## Sum data by using a query

This article explains how to use a type of function called an aggregate function to sum the data in a query result set. This article also briefly explains how to use other aggregate functions, such as COUNT and AVG to count or average the values in a result set. In addition, this article explains how to use the Total Row, a new feature in Microsoft Office Access 2007 that you use to sum data without having to alter the design of your queries.
What do you want to do?
* Understand ways to sum data
* Prepare some sample data
* Sum data by using a Total row
* Calculate group totals by using a totals query
* Sum data across multiple groups by using a crosstab query
* Aggregate function reference

## Sum function on report not working

I have a report created where the page uses a query as the back end, all works well. I need to add a text box with the summed up values; however, I keep getting the #error for the result of my sum function.

I am trying to Sum up one field called SCHDQTY. On the textbox I am trying to put the total into, I have this as the control source:

=Sum([SCHDQTY])

and I get the #Error message when I go into preview view

## Sub Total & Grand Total in a Report

I have a report Like this:

Description, unit_price, QTY, [Total] ( [Total] is an Additional field I added to calculate amount, not from a Query or Table)

A, 2, 10, =sum([unit_price]*[QTY])
B, 2, 10, =sum([unit_price]*[QTY])
C, 2, 10, =sum([unit_price]*[QTY])
-----
Subtotal1 =sum([Total])?

E, 1, 20, =sum([unit_price]*[QTY])
F, 4, 40, =sum([unit_price]*[QTY])
G, 5, 30, =sum([unit_price]*[QTY])
-----
Subtotal2 =sum([Total]) ?
-----
Grand Total = [Subtotal1]+[Subtotal2] ?

I find it difficult to make [Subtotal1], [Subtotal2] and [Grand Total] as [Total] is an additional text box I added to the report to make total. I have tried to use functionGrouping&Totals from Access, but only Count Record is available.

## Trying to calculate total profit margin percentage

I tried this in an unbound text box but it keeps giving me a missing )

=(((Sum([ProdUnitPrice]))-(Sum([ProdUnitCost])))/Sum([ProdUnitPrice])

Basically (total UnitPrice- total Unit cost) /Total Unit Price

## Total a column, data type is causing problem

I have a inventory count column called sum(the amount of stock), a price column, and a total column
ex:
sum price total
1 \$10 \$10
0 \$5 \$0
2 \$2 \$4

what I'd like is for a grand total to be shown in a report or at the bottom, for this example, \$14

the total column is made with a query, multiplying price and sum sum is also a query, adding up the total amount of different stock

currently I had to re-format my total column Total:Format(price*sum),'currency'

When I have tried to do this, I clicked the total button, but my only option is for a count, I need to sum the values, not count how many there are. I am pretty sure it is because the totalcolumn is not actually a price format

I could also just have a report that lists this grand total, but I came up with the same problem.

## How to sum up total hours

Please see the attachment. I want to get a Sum of Duration (mean total hours for that particular date), I simply put Sum in Total field of query but it's showing some error.

## Grand total of sums of various columns in query

I have generated a querry and I have been able to generate the sum of the values of entire column.
Suppose I have two such columns in a querry
How do I find the grand total of sum of two or more columns in a querry

Female children Male Children
Mr x 2 3
Mr Y 1 1

Total 3 4

How do I add the totals of the two columns in a querry without adding another column?
Easier way out is to have a third column "total Siblings" and then add them up to find the grand total. But without doing that how else can we use the sum function in querry for grand total of all columns

## Sub-Report Total added to Report Sub Total Error

I have a report that I am trying to add the Sub-Report Total to, but some of the sub-reports are blank since there is no OT for the specific location. The report is by locations and with the Subreport "OT by Employee per Week" at the end.

How do I not include the sub-report in the subtotal if sub report is empty?

The expression I am using is: =Sum([Total])+[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT]

Sum([Total]) is the Main Report Total
[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT] is the sub-report total

I have tried an IIf statement which still gave me a "#Error" message

## Query Column Totals

I am working on a query and would like to sum the total of one of the columns. I have tried using the "Total" button, but that will only allow me to Count the items in that column. The items are numeric so I'm not sure what the problem is. I also tried the sum function within the query but I get a message that says. You didn't include [Column] as part of your aggregate function. Anyone know what the issue might be

## How to sum up hours

I am trying to total hours worked for the week.

Problem1: group by employee, and sum the hours for each employee per day.

The fields are StartTime, EndTime, HrsWrkDay, hrsWrkweek, the format is 00:00

I have the grouping done but the sum of the hours are not correct. So can I sum time? or do I have to convert the daily time total to something else and then sum the daily?