LiCann
asked on
Account Balance and table design
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.
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.
ASKER
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
BalanceID - id field
CustomerID - FK
AppointmentID - FK
current balance
Balance - balance at that date
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
its almost 1am here, off to bed. good luck and let me know how you go.
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)