We help IT Professionals succeed at work.

Account Balance and table design

LiCann asked
Last Modified: 2013-11-29
I need some help for genius' with math and logic and visuliization.....

I have a form or serveral forms that will show the Account Balance for a customer.  

I have a tblAppointment where the fields include:  ID (PK), Appointment Date, CustomerID, (Fields for payments), and (fields for charges).  I did not design and have to use current structure.

I created a table for tblPayments with  ID foreiegn key, CustomerID, PaymentDate and  (fields for payments)

Both tables have payments (and I can't change right now).

I have created a tblBalance where amounts are sumed by Appointment date and appended to tblBalance when the database is opened using Autoexec.  Fields include ID, CustomerID, Appointmentdate (do I need?), paymentdate, Bal

Back to the main question and the Account Balance (which is  a subform) showing.  Now I need to update/refresh  the Balance when a payment is made in the tblPayment.  This is what I am struggling with.

Do I use just a query to apply the payments to the tblbalance or do I take the payments from tblPayments and calculate the balance and append the new balance to the table?  If so do I need the AppointmentDate or just the Customer ID?  How will this effect balances going forward.

I appreciate the help greatly and need any suggestions available.  Thanks.
Watch Question

I would overload tblBalance with the following fields as it would be easier to remove the redundant fields rather than add them in after the table is populated in production if you are concerned with space issues etc later on.
this means you can immediately get the current balance and show historical account balance without recalculating every time you run your reports etc

BalanceID - id field
CustomerID - FK
AppointmentID - FK
AppointmentDate - "redundant" field to assist queries and to clearly identify the record with the latest current balance
Balance - balance at that date

Your reports and queries would then be easier to write. you can then compare the performance with a "normalised" table structure of tblBalance to determine which is more efficient:
BalanceID - id field
AppointmentID - FK
Balance - balance at that date

but then you would need to join to the other tables for your reports (create a general query joining all the tables on which your reports will be based on)


So use these fields in tblBalance and append payments and charges?

BalanceID - id field
CustomerID - FK
AppointmentID - FK
current balance
Balance - balance at that date
This one is on us!
(Get your first solution completely free - no credit card required)
its almost 1am here, off to bed. good luck and let me know how you go.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.