Make Payment and Deduct from Bill

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.

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

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/WileyT...470525746.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


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.

I think you'll need another table for this. I'd envisage something along the
following lines:

BusinessContacts
.….ContactID (primary key)
…..FirstName
…..LastName
…..etc

Bills
….BillID (primary key)
….DateOfBill
….BillAmt
….ContactID

BillPayments
….BillPaymentID (primary key)
….BillID
….DateOfPayment
….PaymentAmt

With a form based on BusinessContacts, and within it a subform based on Bills,
linked on ContactID, you can then include a control in the subform to show
the current outstanding balance per bill, with a ControlSource of:

=BillAmt - DSum("PaymentAmt", "BillPayments", "BillID = " & [BillID])

Ken Sheridan
Stafford, England

Nathan D Clark wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

gfgfng ,j

"Nathan D Clark" a écrit dans le
message de groupe de discussion :
...
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.