Topics Search

NZ Function on Sum Total

NZ Function on Sum Total
Views: 72
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
Sponsored Links:

More topics

 

Sum data by using a query

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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
 

Query Column Totals

Preview
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
 

Sub-Report Total added to Report Sub Total Error

Preview
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
 

Receiving an #Error message in a form footer sub total

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