AnsweredAssumed Answered

Debit - Credit Amount

Question asked by trevor4370 on Apr 1, 2013
Latest reply on Apr 2, 2013 by philmodjunk

Title

Debit - Credit Amount

Post

     I am at a dead-end and need some help.

     I have a database that records cd purchases. Some clients pay when they order, some pay on pickup and others have a credit balance (The Credit Balance is the one I am having trouble with).

     -----------------------------------------------

     Tables

         
  •           Customers
  •      
  •           Recordings (cd’s)
  •      
  •           Orders
  •      
  •           Credit balance

     Credit balance has

         
  •           IDCredit
  •      
  •           ID
  •      
  •           Add Credit Amount (Add amount for credit amount – Clears every time the list all Orders script runs)
  •      
  •           Credit Balance (calculation – “Add Credit Amount” + “Credit Balance”

     When you add an amount to the “Add Credit Amount” field a calculation adds or subtracts (if neg amount used) the amount to the “Credit Balance”  (No problems here – all works fine).

     Orders have (in short)

         
  •           IDOrder
  •      
  •           Payment Status (Drop list: Credit, To Pay, Cash, No Charge)
  •      
  •           Quantity
  •      
  •           CD Cost (from Recordings table)
  •      
  •           Total Cost

     When a quantity to purchase is entered, the “Total Cost” is calculated based on the “CD Cost” (This works fine).

     This is what I cannot work out.

         
  •           How can you debit the “Total Cost from the “Credit Balance” When a CD is sold to a customer (when “Payment Status = “Credit”)
  •      
  •           The “Total Amount” needs to be debited against the “Credit Balance”. (Credit balance $50.00, total cost $10.00 therefore the Credit Balance = $40.00).
  •      
  •           I can get it to work manually by entering a neg dollar amount (say -10) in the “Add Credit Amount”.
  •      
  •           Not sure how to do it automatically, calculation or script. I would say a Calculation but not sure how to go about it.
  •      
  •           I am not even sure if I have done this the right way.

     Maybe someone could just point me in the right direction that would be a great help – thanks

     Hope I have explained this OK

Outcomes