Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many Relationship
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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 ?
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
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
Now I need some way of producing a grand-total from the sub-totals, is there anyway of doing this?
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.
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?
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.
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.