Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many Relationship

See how to Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many Relationship.

Sponsored Links:

Related Topics

VBA excel worksheet: show only Grand Total
I want to have the Excel file show the data and end with a Grand Total row (number of rows), no subtotals. Anyone know how to do this, please


Grand Total Field ???
as the topic says I am attempting to create a grand total field in my form from multiple data entrys in a subform,
here is what my form looks like with multiple rows of data,

as you can see I have a space for grand total but I dont know how or

what forumula I would use to calculate it does anybody have any ideas ?


Rounding Issues on report display
I have an Access report with several lines of detail with a dollar amount amount calculated by multiplication of salary amounts times certain percentages. This is giving me results of 3-4 decimal places. On the detail line - I formated the amount to currency, and use 2 decimals - and each line is displaying correctly and is rounded correctly. However, I have breaks with summing subtotals in the report - and they are showing a total that is slightly off because it is summing the amount from the detail lines without the 2 decimal place rounding - so a lot of times it is a penny or two off. I have the subtotal amount formatted the same as the detail line - so it displays in a 2 decimal format - but the total is sometimes wrong. Any ideas on how to get my subtotals to correctly add up the rounded amount, and not the unformated/unrounded amount?


Grand total of sums of various columns in query
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


Grand Total Calculation
just a quick question,

I have a report displaying ordered items. The fields shown are [item number], [item name], [units ordered] and [unit price].

In the report design view I've created a calculation so that for every item there is a subtotal using this code:

=[units ordered]*[unit price]

What I want is a "Grand total" in the footer at the bottom of the report. I've tried using:
=sum([subtotal])
but I get "ERROR#" in the box and it sayd the field doesnt exist. I don't understand why Sum doesnt work when
=[subtotal] gives me a value for the last item in the list.

If the text box with the subtotals in is named "subtotal" and the equation works without "sum" in the equation then surely it recognises the field name as an existing field and "sum" should work? Right


Grand-total from sub-total in a report which has been grouped?
I have a report which has been grouped, i've managed to produce a formula which calculates the sub-total for each group.

Now I need some way of producing a grand-total from the sub-totals, is there anyway of doing this?


Invoice Report not adding totasl unless all fields are filled in.
I have an Invoice form that when I run it, it does not give the total unless I have all fields filled in. The totals come from 3 different queries from a form. But when I run the report, it gives me a #error on the blank totals and will not fill in the Grand total unless all total fields are filled in.

See screenshot. What is the best way to fix this? I have the totals in thefooter with an expression of =[Mat_invoice].[Report]![SumOfMaterialTotal]
The Grand Total expression is =[Emp_invoice subreport].[Report]![SumOfEmpTotal]+[Equip_invoice subreport].[Report]![SumOfEquipTotal]+[Mat_invoice].[Report]![SumOfMaterialTotal]
Is there a way to default the empty fields as 0 so the total can be filled? The grand total works if all of the fields are filled in.


Parent form shows "#Error" in total if subform has no records
I've got a grand total field on a parent form that adds the value of two summary fields that are located on subforms of the parent, each one on a different subform. These subforms are looking up transactions from different tables.

One subform DOES allow edits, additions and deletions (EAD's) whereas the other does NOT allow these. They are both "Continuous Forms" typeforms.

The parent grand total field is using the following for a Control Source: [frmProjectBudgetPOsubfrm].[Form]![txtPOTotal] [frmProjectBudgetTrans].[Form]![txtTotal]

The trouble I'm having is that if the subform that does NOT allow EAD's does not find any records in the table, then the parent form's grand total shows "#Error". If the situation isreversed, with a record found in the subform that does NOT allow EAD's but with no record found in the form that DOES allow EAD's, then the parent's total is blank rather than showing anerror.

Does anyone know of a way I can force the parent form's grand total to display "0" instead of "#Error" if the subform that does NOT allow EAD's shows no records?


Subtotals, Formulas & Totals - Query, Form or Report?
Where is the best place to perform calculations? I am working on a fairly simple database. The tables and relationships are set, and confirmed correct.
To summarize, I have a customer table (One to Many) Menu table, Items table and Items detail table(Joins Menu and Items).
I have a form with subform to record Items detail per Menu. I then need to calculate the total cost of items ordered: Line Item Total=[unitprice]*[quantity]. Subtotal= SUM([Line item total])
This is then used to calculate tax,service charge and grand total: Tax=[Subtotal]*0.1,
Service=[Subtotal]*0.2
Grand Total= [Subtotal] [Tax] [Service]

I want to perform these calculations on the sub total of each Menu, not on each line item of the order.
I know calculated fields do not get stored in tables.
Should I put all the calculations in the footer of the form? When I try a query, it won't let me use the calculated field Subtotal in the other calculations.
Should I put the calculations in a report?
I will need to eventually pull data in several formats in reports - Individual Menu, Totals by day, totals by week,etc. and don't want to repeat the calculations on each report.


DB2, MySQL Programmer needs Access Answer
I'm sure you guys have heard this a million times, but here goes.

I need SUBTOTALS and a GRAND TOTAL in a QUERY, not a report, not a form. My users are non-computer-literate.

I need to create a QUERY whose results will be exported to Excel via File > Export. The Excel spreadsheet must look like this:

Mr. John Smith 10 American Way Item-A $30.00
Mr. Dave Doe 20 West Wing St. Item-A $25.00
Ms. Anne Bullock 15 Main Street Item-A $50.00
SUBTOTAL FOR ITEM-A: $105.00

Mrs. Rita Left 100 Wayne Way Item-B $10.00
Mrs. Anne Right 200 West Street Item-B $10.00
Mr. Henry Miller 150 Main Street Item-B $10.00
SUBTOTAL FOR ITEM-B: $30.00

GRAND TOTAL: $135.00
Eventually I plan to use VBA in Excel to make this happen, but I'm not a VB programmer so I'm a little out of my element. I just need something FAST right now.


Displaying a total textbox in a subform
I have a form which displays student information and displays the deposited cheques in a subform for each student.
I am having problems displaying the total of one field inside my subform.
The query that I used for my subform consists of a cheque table.
The field inside my cheque table is as follows (cheque no, cheque date, notes and amount).
I want to total the amount of cheques for each student to show up in the bottom of my subform.


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.


Calculating from a Table
I have a table (table2) that has fields to hold another tables ID (table1) and a currency total.

From a form that relates to the table1 ID, I want to click a button that then totals all of the currency fields from table2 that have the same ID as in table1. The total then is to be inserted into a grand total field in table1.

I have a query that does this, but don't know how to run this in vba and to extract the grand total figure from the query result.

I have also tried to run a for, next routine, but get into difficulties in addrssing the table.


Running Total Per Item In Query
I am trying to create a running total in a way I have never done before and am getting quite frusterated and could really use some help. I am trying to run my query off of a query (I'm not sure if this is how to do it but query1 has the calculated total that I need for query2)
I am using class_name, RiderID, Total_Points. Each rider is in each class multiple times (15 to be exact) and each time they are in a class they get a different total points. I need to calculated the grand total points for each rider in each class (there are 25 classes and hundreds of riders). My end goal for this query is to dump it into a report that sorts the grand total points from highest to lowest grouped by class


Modify Last Page of Report
A few questions to those who know what the heck I am wanting from this.

First is it possible to format a field on the last page of a report.
(The last page is a grand total from the various year end sections of the report and I am needing to show what years are covered by the grand total.)

If it is possible to format a field on the last page of a report as I have stated above, is it also possible to do the same when multiple reports are being generated at the same time?
(I have some clients that have data that covers a spread of years, I have it possible to print all clients at once with each client having it's own year end and year end grand total. Thus the Grand Total Page will need to show all the years it covers even though it is not the last page of the report set, but is the last page of that particular client.)

I'll be glad to answer any questions that I am sure you will have


Sub Total and Grand Total on calculated columns?
I have to develop a report that will have subtotal and grand total result.

The report will have 2 detail section results for different groups of data. I have them calculated ok for subtotal (of 2 groups) and grand total. However, when I was given the assignment to add 3 more columns A, B, C. The column A based on IIf statement and formular calculation from previous columns. Then columm B calculation based on the column A, then columm C calculation based on the column B.

When I tried to subtotal and grand total column for the 3 columns A, B, C, I encountered problems! Either I get a "Overflow!" and no report result at atll! Or I can see the result of first pge of data, but when I click the arrow to go to next page, it jumps to the Design View mode of report and does NOT let me view the next page of report in View mode at all!

Please advise WHAT I did wrong, and what should I fix it


Expressions on a form is displaying incorrect calculations with negative values
I have a formula created in an expression in a textbox on a form in access 2010: start time subtract end time multiplied by a cost price, but the total keeps displaying in a negative value I.e -$12.which is incorrect is there a way to rectify this possibly in properties or else where. I'm not sure so it displays the correct value?


Report does not generate a correct Grand Total
I have a crosstab query in MS Access that I have turned into a report. Whenever I try to use a label to calculate the grand total of a field, I will either get the last value entered in that field, a 0 value or "#Error" inside the label.


calculated field displays value only if all the values are complered
Basically I have a field that calculates the sum of other subtotals like this:

=[SubTotal1]+[SubTotal2]+[SubTotal3]

But the value appears only if every subtotal has a value, I want it to still show the value if for example subtotal 2 has no value in it.

I try to set the default property of the subtotals to 0, but nothing happens.

p.s there are some cases where the value appears and dissapears


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.