Topics Search

NZ Function on Sum Total

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
Sponsored Links:

Similar posts...

 

Sum data by using a query

Sum data by using a query Icon
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

Sub Total & Grand Total in a Report Icon
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

Trying to calculate total profit margin percentage Icon
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

Total a column, data type is causing problem Icon
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

Sub-Report Total added to Report Sub Total Error Icon
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

Query Column Totals Icon
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

How to sum up hours Icon
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

Sums in Detail area of report Icon
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

Receiving an #Error message in a form footer sub total Icon
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

Summing values in report view Icon
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.