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.