musicradiolive Posted October 16, 2007 Share Posted October 16, 2007 Hi.I'm creating an online payment system for my company, but i am encountering a few problems.DATABASEtblItems¦- id (Auto Number)¦- ItemNumber¦- IDescription¦- Qty¦- UnitPrice¦- Invoice¦- TDatetblInvoices¦- id (Auto Number)¦- IDate¦- Amount¦- Client¦- AmountDue¦- DueDate¦- InvoiceNumber¦- Rep¦- Shipping¦- Tax1¦- Tax2tblPayments¦- id (Auto Number)¦- IDate¦- InvoiceNumber¦- Amount¦- Method¦- RunningTotal <%strSQL = "Select * FROM tblItems WHERE Invoice = '" & Request.QueryString("id") & "'"rs.Open strSQL, strCondo while not rs.eoftheVarA = rs("Qty")theVarB = rs("UnitPrice")theOperator = "Multiply"if theOperator = "Multiply" then theAnswer = theVarA * theVarB%> A table then lists the items in the invoice and the QTY is multiplied by the Amount to create the total of that part of the invoice.I then need to take each of those totals to create a subtotal. Then i will pull the shipping amount, the tax1 and the tax2 from the databse and i want to then calculate subtotal + shipping + tax1 + tax2 to create the grand total.Next, i want to pull payments from the tblPayments.When a payment is made online the details are sent back to the database, and i want that total to be deducted from the invoice to show how much is outstanding.So basically GrandTotal - Each payment.Thanks in advance.Jon Link to comment Share on other sites More sharing options...
justsomeguy Posted October 16, 2007 Share Posted October 16, 2007 What part do you need help with? All this requires is a little database work (which it looks like you're already doing) and some math. Link to comment Share on other sites More sharing options...
musicradiolive Posted October 16, 2007 Author Share Posted October 16, 2007 Hey,Thanks for the reply.I'm stuck with the calculations.Basically, i am pulling the tblItems recordset to create the invoice, so it create the invoice list.For each item i have managed to use a calculation (shown in the above code) to create the total for that items. The calculation takes the QTY * the UnitPrice to create the answer, the total amount is then put into a text box which is read only.To make each text box unique i have made it so it names it using the rs("id") from tblItems. <input readonly="yes" style="background-color:#FFFFCC" size="4" name="<% =rs("id") %>" type="text" value="<% Response.write(theAnswer) %>"> The next bit is the shipping, tax1 and tax2 from tblInvoices. I want to take the values of the items and add the shipping, tax1 and tax2 to create the GrandTotal. Next is the payments, so when a customer pays an amount of the invoice is deducts it from the grand total.When a customer pays it will put the details in tblPayments. The payments will be shown on the invoice the same way the invoice is shown using the id field to make each text field unique.I want to take the grand total - each payment = AMOUNT OUTSTANDING.I hope that makes sense and appreciate your help.ThanksJon Link to comment Share on other sites More sharing options...
justsomeguy Posted October 16, 2007 Share Posted October 16, 2007 Well, the subtotal should be obvious, that's just a sum of all the item totals. The tax is the subtotal multiplied by whatever tax rate you want to use, then you add the shipping, tax, and subtotal to get the grand total. If you record payments in another table then you can get a list of those and total them up to subtract that amount from the grand total to get the amount outstanding. Link to comment Share on other sites More sharing options...
musicradiolive Posted October 17, 2007 Author Share Posted October 17, 2007 How do i write the code for the maths though!!I know the calculations, but i cant work out how to make the code take each item within the invoice to make them add together etc. Link to comment Share on other sites More sharing options...
justsomeguy Posted October 17, 2007 Share Posted October 17, 2007 This is how you create the subtotal: subtotal = 0while ... subtotal = subtotal + item_totalwend And voila, you have a subtotal. The plus operator makes two numbers add together. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.