Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Question Unanswered: Calculating Change.....

    Hi,

    Calculating the change to give back to my customers sounds easy, but in my system, i take into account multiple payments and multiple payment types.

    A good example of this, is when a customers buys a products, for let's say 845.00$, and puts a 500$ deposit. Later on, he comes back to give me a 45$ deposit, and gives me a 50$ bill. I need to calculate the 5$ change i owe him.

    Right now, i have a two tables, tblOrders, and tblOrderPayments. In tblOrderPayments, i have a field for every type of payment we accept. In tblOrders, i have a total owed field. This was done in order to manage multiple payments per OrderID.

    I also thought about having a combobox in tblOrderPayments that is populated by a third table that contains all payment types. This would reduce the number of fields in tblOrderPayments, which might simplify the query.

    I'm a little at a loss about which direction to take...Any one ever put something like this together?

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Assuming you're using a form

    In your Invoices Items table (you may be calling this Orders) you should have the ability to calculate the sum of an Invoice by adding all the Invoice Item ID entries for one particular purchase by one particular customer. In your OrderPayments table (usu an AR table) you should be able to sum the total given to you by a particular customer (not sure if you care about total AR or Order-Level AR).

    These summations could be stored queries along with other relevant data to link to your form, base subforms on these summation queries and link the subforms onto your main form. You can then address the contents of the sum field from each and do the subtraction that way.

    Good luck. It's not too difficult, but quite time consuming for first set up.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    Yes, this is in forms, and i use queries for most of my calculations. I use the common naming convention, tbl for tables, frm for forms and so on.

    Here's the structure i have now:

    frmOrderDetails ( bound to a query )
    intOrderID
    curUnitRetail
    intQty
    curExtendedPrice

    frmOrders: ( bound to a table )
    curSubtotal ( calculated from a query that calculates extended prices )
    curTx1 ( calculated from the region of the customer )
    curTx2 ( calculated from the region of the customer )
    curTx3 ( calculated from the region of the customer )
    curShipping
    curTotalDue ( sum of all the above fields )

    frmOrderPayments ( bound to a query )
    intOrderID
    datPaymentDate
    curAmtVisa
    curAmtMaster
    curAmtCash
    curExtendedPayment ( sum of all the payments for a single date )

    I need to be able to give change for a single payment. The total this single payment comes to might be lower than the Total Due for the invoice....

    Like in my example, how do a calculate the change if the Total Due is greater?

    thanks for the help!

    Cheers!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm surprised you are trying to do it this way which probably means i don't understand the question.

    i would be tempted to divorce payments from orders just a little bit.

    in your current structure, a customer can owe you 1000 on one order but you give him change when he pays you 80 of another 100 order

    basically you need something that (for a given customer) keeps a tally of debits and credits. e.g.

    tblCrDr:
    customerID 'which i dont see in your structure
    dblCrDr 'the amount that got invoiced or paid
    datWhen 'and when it happened

    so now you can work out what each customer owes you (or you owe him) on any given date including now()

    you don't want too much separation between orders & payments or you can't track down which order remains unpaid, so add
    intOrderID 'the order concerned

    and you should almost certainly add a
    datDue 'when it should be paid

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    Hi Izy,

    I have all the requisite ID fields for the customers, orders and products. I only listed here my Currency fields. The most complex scenario ( that we deals with more often than i'd like ) is when a customer owes us a total of let's say 543.00 and wants to make a deposit of 43.00 and gives me a 50$ bill. I need to show the change in a field. Obviously, i cant calculate from only the total amount due, and the amount received. What would complicate matters even more is if a customer what already made a 100$ cash payment on an invoice, has a balance of 543.00 and wants to pay the 43$ and gives me a 50$...

    I think i wont have a choice to split the amount of the payment into two seperate fields, one for the amount received, and the other for the amount paid.... i was just curious to see if any of the experts had better ideas...

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •