Topics Search

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

Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many Relationship
Views: 80
See how to Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many Relationship.
Sponsored Links:

More 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:
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,
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

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

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.