Make Payment and Deduct from Bill

[Nathan D Clark]

Brief Summary: I want to be able to make a Payment Amount on a form to a Bill
Amount, then the Bill Amount will reduct to show an Ending Balance on the
Form.

Details:
I have a table called BusinessContacts with ContactID as the Primary ID
Field. This table is dedicated to storing data on each Bill (Name, Address
etc.)

On the Business Form which is based on the BusinessContacts table, I have a
Payment Button this opens another form. The second form is called Bill
Payment, and it is based on a table called BillPayment with a Primary ID
Field named BillPaymentID. This form is used to make a payment to the Total
Starting Balance of the Bill which is linked to ContactID on BusinessContacts
table.

Diagram of Setup:
BillPayment: Table -----------------+BusinessContacts: Table
ContactID (TextField)---------------+ContactID (PrimaryID, AutoNumber)
BillPaymentID (PrimaryID)
DateOfPayment
StartingBalance
PaymentAmt
EndingBalance

I created a Query to total all the PaymentAmt named TotalOfAllPaymentsAmt
which does product a Sum of the PaymentAmt column of BillPayment Table.

I am unsure where to go from here. I need to display the balance on the
Business form after making a payment to the total bill.

 

[Tom van Stiphout]

On Sat, 6 Mar 2010 10:08:05 -0800, Nathan D Clark

Hi Nathan,
You seem a bit lost. This data structure is not going to work for
several reasons. Discussing the finer points of accounting tables is
more than we can do in a newsgroup post.
I have a suggestion: do yourself a favor and pick up this book:
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470525746.html
(I am not associated with it, but I know several of the authors)
This book has several chapters that should be right up your alley.

-Tom.
Microsoft Access MVP

 

[joelgeraldine]

gfgfng ,j

"Nathan D Clark" <[email protected]> a écrit dans le
message de groupe de discussion :
(e-mail address removed)...