Calcuation Error in Query
Have created a database for Payments entry for various parties in which I
have created a Select Query containing Details such as Bill value, Advance Paid, Bill Accounted, TDS, WCT,Balance Payable for Adv.Bill, 1st RA Bill, 2nd RA Bill, 3rd RA Bill & Final Bill.
I have given calculations based on Bill Value(s) for obtaining Bill Accounted, TDS, WCT & Balance Payable for all the Bills(1st, 2nd, 3rd RA & Final Bills).
I am getting all the calculation right except in the "Advance Paid" coloumn where I am supposed to get the total of all Advances Paid beginning from Advance Bill to Final Bill. If enter bill values in all corresponding Bill Sections (created individual Subforms for all RA Bills & Final Bills) only, the Total is displaying in all the subforms whereas if I enter Bill values only for 2 Bills (For example in Advance & 2nd RA Bill), the Total of Advances paid till the previous bill is not showing in the coloumn "Advance Paid". The following is the calculation I have given for each bill(s):
SELECT [Party Payments].OrderID, [Party Payments].[Advance Bill Value], CCur(Nz([Party Payments].[Advance Bill Value],0)*2.266/100) AS [TDS on Adv Bill], CCur(Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on Adv Bill],0)) AS [Net Adv Payable], [Party Payments].[Cheq/DD No], [Party Payments].[Cheq/DD Date],
[Party Payments].[1st RA Bill No], [Party Payments].[1st RA Bill Value], CCur(Nz([Advance Bill Value],0)) AS [Adv Paid(Gross)], CCur(Nz([Party Payments].[1st RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)) AS [1st RA Bill Accounted], CCur(Nz([1st RA Bill Accounted],0)*2.266/100) AS [TDS on 1st RA], CCur(Nz([1st RA Bill Accounted],0)*4/100) AS [WCT on 1st RA], CCur(Nz([1st RA Bill Accounted],0)*5/100) AS [5% on 1st RA], CCur(Nz([Party Payments].[1st RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on 1st RA],0)-Nz([WCT on 1st RA])-Nz([5% on 1st RA],0)) AS [1st RA Payable], [Party Payments].[Cheq/DD No(1st RA)], [Party Payments].[Cheq/DD Date(1st RA)],
[Party Payments].[2nd RA Bill No], [Party Payments].[2nd RA Bill Value], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)) AS [Adv Paid 1st RA(Gross)], CCur(Nz([Party Payments].[2nd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([1st RA Bill Accounted],0)) AS [2nd RA Bill Accounted], CCur(Nz([2nd RA Bill Accounted],0)*2.266/100) AS [TDS on 2nd RA], CCur(Nz([2nd RA Bill Accounted],0)*4/100) AS [WCT on 2nd RA], CCur(Nz([2nd RA Bill Accounted],0)*5/100) AS [5% on 2nd RA], CCur(Nz([Party Payments].[2nd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on 2nd RA],0)-Nz([WCT on 2nd RA])-Nz([5% on 2nd RA],0)-Nz([1st RA Bill Accounted],0)) AS [2nd RA Payable], [Party Payments].[Cheq/DD No(2nd RA)], [Party Payments].[Cheq/DD Date(2nd RA)],
[Party Payments].[3rd RA Bill No], [Party Payments].[3rd RA Bill Value], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)+Nz([2nd RA Bill Accounted],0)) AS [Adv Paid 2nd RA(Gross)], CCur(Nz([Party Payments].[3rd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)) AS [3rd RA Bill Accounted], CCur(Nz([3rd RA Bill Accounted],0)*2.266/100) AS [TDS on 3rd RA], CCur(Nz([3rd RA Bill Accounted],0)*4/100) AS [WCT on 3rd RA], CCur(Nz([3rd RA Bill Accounted],0)*5/100) AS [5% on 3rd RA], CCur(Nz([Party Payments].[3rd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([TDS on 3rd RA],0)-Nz([WCT on 3rd RA],0)-Nz([5% on 3rd RA],0)) AS [3rd RA Payable], [Party Payments].[Cheq/DD No(3rd RA)], [Party Payments].[Cheq/DD Date(3rd RA)],
[Party Payments].[Final Bill No], [Party Payments].[Final Bill Value(75%)], [Party Payments].[Excess Qty], CCur(Nz([Party Payments].[Final Bill Value(75%)],0)-Nz([Party Payments].[Excess Qty],0)) AS [Amt Before Prorata], CCur(Nz([Amt Before Prorata],0)*75/100) AS [Pro Rata Value], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)+Nz([2nd RA Bill Accounted],0)+Nz([3rd RA Bill Accounted],0)) AS [Adv Paid 3rd RA(Gross)], CCur(Nz([Pro Rata Value],0)-Nz([Adv Paid 3rd RA(Gross)],0)) AS [Pro Rata Accounted], CCur(Nz([Pro Rata Accounted],0)*2.266/100) AS [TDS on Final Bill(75%)], CCur(Nz([Pro Rata Accounted],0)*4/100) AS [WCT on Final Bill(75%)], CCur(Nz([Pro Rata Accounted],0)*5/100) AS [5% on Final Bill(75%)], CCur(Nz([Pro Rata Accounted],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on Final Bill(75%)],0)-Nz([WCT on Final Bill(75%)],0)-Nz([5% on Final Bill(75%)],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([3rd RA Bill Accounted],0)-Nz([Excess Qty],0)) AS [Final Bill(75%) Payable], [Party Payments].[Cheq/DD No(Pro-rata)], [Party Payments].[Cheq/DD Date(Pro-rata)],
[Party Payments].[Final Bill Value], [Party Payments].[Audit Deductions], [Party Payments].[Other Misc Deductions], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)+Nz([2nd RA Bill Accounted],0)+Nz([3rd RA Bill Accounted],0)+Nz([Pro Rata Accounted],0)) AS [Adv Paid 75%(Gross)], CCur(Nz([Party Payments].[Final Bill Value],0)-Nz([Pro Rata Accounted],0)-Nz([3rd RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([1st RA Bill Accounted],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([Party Payments].[Audit Deductions],0)-Nz([Party Payments].[Other Misc Deductions],0)) AS [Final Bill Accounted], CCur(Nz([Final Bill Accounted],0)*2.266/100) AS [TDS on Final Bill], CCur(Nz([Final Bill Accounted],0)*4/100) AS [WCT on Final Bill], CCur(Nz([Final Bill Accounted],0)*5/100) AS [5% on Final Bill], CCur(Nz([Party Payments].[Final Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on Final Bill],0)-Nz([WCT on Final Bill],0)-Nz([5% on Final Bill],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([3rd RA Bill Accounted],0)-Nz([Pro Rata Accounted],0)-Nz([Party Payments].[Audit Deductions],0)-Nz([Party Payments].[Other Misc Deductions],0)) AS [Balance Payable], [Party Payments].[Cheq/DD No(Final)], [Party Payments].[Cheq/DD Date(Final)]
FROM Orders INNER JOIN [Party Payments] ON Orders.OrderID = [Party Payments].OrderID;
Pls. help me out in this. I dont know whether it is the right procedure or not. Is there any other way of getting the result.