Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Thumbs up Unanswered: Transactions, Payments and Balances (oh my!)

    I'm working on an Access 2000 Database to track jobs.

    Several tables including customers, jobs, transactions, payments

    I think (I'm sure) I made a design mistake.

    The transactions are working fine, and are added to each job.
    (an example of a transaction would be a visit to that customers job.
    rate*hours+materials etc..)

    I have a form that I use to apply payments, this form is bound to the 'payments' table. When a payment is recieved, I want to apply it to the oldest outstanding transaction for that client.

    I have queries that find the oldest oustanding transaction and pay it, what I'm struggling with is if they pay more than the amount of one transaction, I'd like to carry the balance to the next newest transaction.
    until I use the entire payment. (each customer can have multiple jobs and multiple 'transactions' for that job.)

    Any help would be appreciated, I've got most of it working except this part (carrying over the extra for the next transaction) I can calculate the overpayment, but and put it in the table, but can't seem to use it from there.)

    Thanks in advance.

    -MDH

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    - Open a recordset of outstanding transactions sorted ascending by date.
    - store the amount paid in a variable amtLeft
    - Start the first loop through the recordset
    if amtLeft < amountowing then
    thispayamount = amtLeft
    amtLeft = 0
    else
    thispayamount = amountowing
    amtLeft = amtLeft - amountowing
    end if

    - write a temp query definition (qdf) to insert the payment into the transaction table for thispayamount
    - stop the loop

    if amtleft > 0 then
    - write a temp qdf to insert a credit into the transaction table for amtleft
    end if

  3. #3
    Join Date
    Nov 2003
    Posts
    3
    Originally posted by jmrSudbury
    - Open a recordset of outstanding transactions sorted ascending by date.
    - store the amount paid in a variable amtLeft
    - Start the first loop through the recordset
    if amtLeft < amountowing then
    thispayamount = amtLeft
    amtLeft = 0
    else
    thispayamount = amountowing
    amtLeft = amtLeft - amountowing
    end if

    - write a temp query definition (qdf) to insert the payment into the transaction table for thispayamount
    - stop the loop

    if amtleft > 0 then
    - write a temp qdf to insert a credit into the transaction table for amtleft
    end if
    Thank you for the reply, (it took me a moment to follow the logic, but I'm sure I can get it to work) The only problem is (and I think I'd be done by now if I could get around this) I can't seem to get the 'amountowing' into a variable that I can work with. My payment form is bound to the payments table, and I can't seem to extract the value from another query. (I have the queries working, but can't seem to call them up, maybe that's what you meant by opening the recordset.)
    Any suggestions on this would be great.

    Thank you again!

    -MDH

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The amount owing is in the query of the recordset. The amount owing is the total owing for the transaction that has not yet been paid. In your transaction table, you have a job id foreign key of some sort. If you need to, you can do a sum grouped by that foreign key and pay off jobs at a time. I have a billing code for each installment in my transaction table that I group by. All bills and payments get the same code for the first installment and another code for the second installment. As the first installment gets paid, it's sum for that job will eventually get to 0. If it is not paid by it's due date, then interest is added.

  5. #5
    Join Date
    Nov 2003
    Posts
    3
    Originally posted by jmrSudbury
    The amount owing is in the query of the recordset. The amount owing is the total owing for the transaction that has not yet been paid. In your transaction table, you have a job id foreign key of some sort. If you need to, you can do a sum grouped by that foreign key and pay off jobs at a time. I have a billing code for each installment in my transaction table that I group by. All bills and payments get the same code for the first installment and another code for the second installment. As the first installment gets paid, it's sum for that job will eventually get to 0. If it is not paid by it's due date, then interest is added.
    Thanks again,
    (sorry, about this, but I'm still a little new)

    I have balances for outstanding transactions in my transactions table, but not in the payments table. The payments table just has the customer, date, job etc.. The Transaction table Does have a key (I think that's what you referred to) that ties it to the customer and the job.

    I'm still not sure how to 'call up' the query and get the outstanding balance. If I change the data source on the form, then the form doesn't work for payments. (I've tried adding buttons and text boxes, but can't retrieve the 'amountowing' or balance for the transaction from the transaction table.

    Thanks again.

    -MDH

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Since you have 2 separate tables, you will need 3 queries to make sure access does the sums correctly. I haven't tried it, so off the top of my head:

    Select JobId, sum(PayAmount) as PayAmt from Payment group by jobid order by jobid; (save as PQy)

    select jobid, sum(Amount) as Amt from Transactions group by jobid order by jobid; (Save as TQy)

    Select jobid, amt - payamt as AmountOwing from TQy INNER JOIN PQy ON TQy.Jobid = Pqy.Jobid where amt-payamt <>0;

    Change the field and table names as needed, and use the third query for your recordset.

Posting Permissions

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