# NZ Function on Sum Total

Views: 75
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

## 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.

## 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.

## 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

## 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?

## Sums in Detail area of report

In a report I am using the underlying query to calculate extended row totals for 2 different values, Value A and Value B. Each are calculated in the query by CostA*Qty or Cost B*Qty and these work just fine. They also relate in the query to a group, like Group 1 or Group 2.

Then in the report detail area, Group 1 the sum of the CostA*Qty and sum of CostB*Qty work also (text boxes) - BUT I then sum the A total sum and the B total sum into a sub-grand total(text box, again in the detail area) - which works fine unless there is no data in one of the A or B sums.

Example: Sum A = \$1,000, Sum B = \$200, Sub Grand Total = \$1,200 but if
Sum A = \$1,000 and Sum B = blank, Sub Grand Total = blank too.

## Receiving an #Error message in a form footer sub total

I have a customer database that I use and want to create quotes from; however, the form footer sum function is not working. I need to have it multiple the size by price by qty.

Both the price and qty field are text boxes but the size field is a lookup field. I understand that the sum function is not able to use calculated fields. Is there away around this to make my sum work?

## Summing values in report view

I need the syntax to sum the numbers indicated in the attachment in report view. Just adding a sum function like =sum([Fee]) does not come up with the correct total because the fees are not stored for each record.

They come from a separate table that was created to calculate the fees based on the user module and some other criteria.

I know there is a way to sum a group of numbers when the report is in report view, I just don't know what it is.

## Help with Sum and DSum

I'm trying to make a daily report where I place the SUM for only that day next to the complete total SUM of all previous days (a running total).

I'm just not sure how I can selectively SUM for that day. I tried using =DSum but I get #Error all the way down my report. I do not know what to use for the domain. Do I use the same query I used in report wizard?

## Doing a SUM from multiple records

My Question is that I have several records on multiple rows, is there a way to sum everything up as one. As I think in my query I have a count function and it is counting multiple records & I need one value which is the sum of all these records.

I am not using an increment of ID on each record so I cannot use those values. So based on the query, the data would be

-----
UserID | Type |
-----
101 3
101 4
101 1
101 10

I need to sum this to be Total = 18 AS TotalColumn

is there a way to do that?