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