NZ Function on Sum Total
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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
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:
and I get the #Error message when I go into preview view
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])
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.
Basically (total UnitPrice- total Unit cost) /Total Unit Price
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.
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
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
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?