Calculate total amt due on an invoice.
in [Reports]
|
From: Dennis on 4 Apr 2010 00:41 Hi, I'm using Access via XP Office Pro on Windows 2007. Tables: tblWorkOrder – contains all the parent level information for the work order / invoice Key – WorkOrder – automatically assigned number Work order data tblWorkOrderDet – contains all the child / transaction level information for the work order. Key: WorkOrderSeqNo – this is an automatically assigned keky Data: WorkOrder There are four transaction types contained in this table: 1. Comments on the work done on this work order. 2. Labor charges Quantity (Hours) * Rate 3. Part charges Quantity * Price per (Rate) + Shipping + Tax 4. Expense Quantity (miles) * Rate Background: I am working on an invoice report. The report has the main report and a sub-report. The report is grouped by invoice / work order number. I have a group heading called WOHeader and a group footing called WOFooter. The main report's row source is the tblWorkOrder table. The sub-report's row source is tblWorkOrderDet. The sub-report contains the following fields: txtTransDate, txtWorkDesc (which is built using multiple fields), txtQuantity, txtRate, txtTaxAmt, txtShipping, and txtTotAmtDue (=txtQuantity * txtRate). I want to total the tax amt and shipping and show them on the bottom of the invoice. They are currently hidden fields. I understand that I need to have hidden “Running Sum” text boxes on the group heading for the three amounts (total due, tax, shipping). The visible boxes for these amounts are in the group footing. I've read the forum discussion entitled Referencing sub-report fields on main report: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=sub-report+variable&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.access.reports&p=1&tid=2928e41c-db3a-43c0-a03d-01389ddddec1 Question: I trying following the instructions in that discussion to calculate the following totals in WOHeader: 1. Total amount due (Quantity * rate). 2. Total Shipping 3. Total Tax. Let's just talk about the first field because once I get that one working, I can get the other fields. In the WOHeader, I have a text box called txtWOHAmtDue. It's control source is: =rptInvoice_srDet.Report!txtAmtDue And it's Running Sum property is set to Over group. For debugging, this box is visible. When I run my test, I have an invoice with three labor transactions. The Total Amt due for each transaction is: 1. $45.00 2. $11.25 3. $ 0.00 The total in my txtWOHAmtDue is $45, not the expected 55.25. It appears to only be including the total from the first deail. What am I doing wrong? Dennis
From: Marshall Barton on 4 Apr 2010 09:13 Dennis wrote: >I�m using Access via XP Office Pro on Windows 2007. > >Tables: >tblWorkOrder � contains all the parent level information for the work order >/ invoice >Key � WorkOrder � automatically assigned number >Work order data > > >tblWorkOrderDet � contains all the child / transaction level information for >the work order. >Key: WorkOrderSeqNo � this is an automatically assigned keky >Data: WorkOrder > >There are four transaction types contained in this table: >1. Comments on the work done on this work order. >2. Labor charges Quantity (Hours) * Rate >3. Part charges Quantity * Price per (Rate) + Shipping + Tax >4. Expense Quantity (miles) * Rate > >Background: >I am working on an invoice report. The report has the main report and a >sub-report. The report is grouped by invoice / work order number. I have a >group heading called WOHeader and a group footing called WOFooter. > >The main report�s row source is the tblWorkOrder table. The sub-report�s >row source is tblWorkOrderDet. > >The sub-report contains the following fields: >txtTransDate, txtWorkDesc (which is built using multiple fields), >txtQuantity, txtRate, txtTaxAmt, txtShipping, and txtTotAmtDue (=txtQuantity >* txtRate). I want to total the tax amt and shipping and show them on the >bottom of the invoice. They are currently hidden fields. > >I understand that I need to have hidden �Running Sum� text boxes on the >group heading for the three amounts (total due, tax, shipping). The visible >boxes for these amounts are in the group footing. > >I�ve read the forum discussion entitled Referencing sub-report fields on >main report: > >http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=sub-report+variable&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.access.reports&p=1&tid=2928e41c-db3a-43c0-a03d-01389ddddec1 > > >Question: >I trying following the instructions in that discussion to calculate the >following totals in WOHeader: >1. Total amount due (Quantity * rate). >2. Total Shipping >3. Total Tax. > >Let�s just talk about the first field because once I get that one working, I >can get the other fields. > >In the WOHeader, I have a text box called txtWOHAmtDue. It�s control source >is: >=rptInvoice_srDet.Report!txtAmtDue >And it�s Running Sum property is set to Over group. For debugging, this box >is visible. > >When I run my test, I have an invoice with three labor transactions. The >Total Amt due for each transaction is: > >1. $45.00 >2. $11.25 >3. $ 0.00 > >The total in my txtWOHAmtDue is $45, not the expected 55.25. It appears to >only be including the total from the first deail. A runningsum is unknown until the end of the group so the header only sees the first line of the running sum. That was the end of the discussion in earlier(?) versions of Access, In later versions the group header can refer to a total in the group footer. So, first put the total you want in the group footer and then the group header can refer to that total. -- Marsh MVP [MS Access]
From: Dennis on 4 Apr 2010 12:22 Marshall, Your comment: So, first put the total you want in the group footer and then the group header can refer to that total. My response: Ok, I tried that and it did not work my subreport name is rptInvoice_srDet The name of the control on the sub-report is txtAmtDue. Note: txtAmtDue source = (Quantity & Rate) The control on the footer is: txtWOFLaborAmt The source =rptInvoice_srDet.Report!txtAmtDue Format = Currency Decimal Places = 2 Running sum = Over Group. The first line txtAmtDue control is 45.00 The second line txtAmtDue control is 11.25 The third line txtAmtDue control 0.00 When I run the invoice, the amount in txtWOFLaborAmt = 0 Any suggestions? Dennis
From: Dennis on 4 Apr 2010 12:28 Marshall, I just need a total in the group footing. I just put it in the header because that is what one discussion suggested. I also tried putting a sum amount in the footer of the sumform. I could not get that to work either. Dennis
From: Marshall Barton on 5 Apr 2010 10:00
Dennis wrote: >I just need a total in the group footing. I just put it in the header >because that is what one discussion suggested. > >I also tried putting a sum amount in the footer of the sumform. I could not >get that to work either. > You need a subreport report footer) text box (named txtTotal) with an expression like: =Sum(Quantity * Rate) Then a main report rext box in the same section as the subreport can use the expression: =rptInvoice_srDet.Report!txtTotal or, as I prefer: =rptInvoice_srDet.Report.Report.txtTotal -- Marsh MVP [MS Access] |