I have a table where I need to create a running total column, but its not as simple as a regular totals query. The underlying table is a charges table from a linked MySQL database, from which I did not design nor am i able to modify any structure of, and I am creating the query in Access.
The table consists of all debits and credits associated with all customers accounts. A summary of the fields, at least the ones needed on my query, are "customerID", "ChargeDate", "ChargeTypeID", "StartDate", "Endate", and "Amount". All of the amounts are positive numbers. If the "ChargeTypeID" field is <=1 (yes, I know, negative ID fields???) then that charge is a debit to the account. Anything greater than 1, depending on the value is rather a credit or debit to the account.
It seems that the ChargeTypeID field is referencing 2 different tables... If it has a positive number it is referencing the chargeType table (1 = Regular, 2= Credit, 3 = Payment, 4 = Finance Charge, etc.). If it has a negative number it seems that it is referencing a BillRate table which holds different rates (-1 = $25/mo, -2= $28/mo, -3 = $32/mo, etc.). The ID's are stored as a positive number in the BillRate table however.
I don't know if that before mentioned table reference would have any bearing on what i need to accomplish, but thought mentioning it wouldn't hurt.
So what I need is, I am creating a customer history subform that displays every charge, payment, credit, etc. ever posted to their account and need the running balance to display after each entry. So an example of what I want to display is:
Date Type Amount Period Balance
11/1/12 -5 $25.00 11/1/12 - 11/30/12 $25.00
11/5/12 3 $25.00 $0.00
12/1/12 -5 $25.00 12/1/12 - 12/31/12 $25.00
12/5/12 4 $5.00 $30.00
12/8/12 3 $15.00 $15.00
...etc.
For the Type field, I wouldn't actually be displaying the ID but I'd lookup the description behind the ID and display that. Also the Period field needs to be 1 field that I would consolidate the Start/End Dates into 1 field. Remember that all amounts in the table are POSITIVE numbers.
Originally I thought I can use a "if" function to determine if the type is a debit or credit, then display the amount in positive or negative format, then use a totals query on that field, but it wont let me use a totals query when using an expression like that. Any suggestions on how to do this with this scenario?
Two additional things to mention. First, as already mentioned the amounts are in positive numbers, but ALSO are not in decimal format. So an amount of $25.67 is stored in the table as 2567 and $1.99 is stored as 199.
Second, it seems that this running total is hard enough to acomplish, but if even possible at all, is there a way to display it with the newest records on top and older ones below it? So it would be a reverse order of the example listed above. The customers table holds the current balance, so you may be able to use this as a starting point to reverse calculate the running balance in this situation.
The table consists of all debits and credits associated with all customers accounts. A summary of the fields, at least the ones needed on my query, are "customerID", "ChargeDate", "ChargeTypeID", "StartDate", "Endate", and "Amount". All of the amounts are positive numbers. If the "ChargeTypeID" field is <=1 (yes, I know, negative ID fields???) then that charge is a debit to the account. Anything greater than 1, depending on the value is rather a credit or debit to the account.
It seems that the ChargeTypeID field is referencing 2 different tables... If it has a positive number it is referencing the chargeType table (1 = Regular, 2= Credit, 3 = Payment, 4 = Finance Charge, etc.). If it has a negative number it seems that it is referencing a BillRate table which holds different rates (-1 = $25/mo, -2= $28/mo, -3 = $32/mo, etc.). The ID's are stored as a positive number in the BillRate table however.
I don't know if that before mentioned table reference would have any bearing on what i need to accomplish, but thought mentioning it wouldn't hurt.
So what I need is, I am creating a customer history subform that displays every charge, payment, credit, etc. ever posted to their account and need the running balance to display after each entry. So an example of what I want to display is:
Date Type Amount Period Balance
11/1/12 -5 $25.00 11/1/12 - 11/30/12 $25.00
11/5/12 3 $25.00 $0.00
12/1/12 -5 $25.00 12/1/12 - 12/31/12 $25.00
12/5/12 4 $5.00 $30.00
12/8/12 3 $15.00 $15.00
...etc.
For the Type field, I wouldn't actually be displaying the ID but I'd lookup the description behind the ID and display that. Also the Period field needs to be 1 field that I would consolidate the Start/End Dates into 1 field. Remember that all amounts in the table are POSITIVE numbers.
Originally I thought I can use a "if" function to determine if the type is a debit or credit, then display the amount in positive or negative format, then use a totals query on that field, but it wont let me use a totals query when using an expression like that. Any suggestions on how to do this with this scenario?
Two additional things to mention. First, as already mentioned the amounts are in positive numbers, but ALSO are not in decimal format. So an amount of $25.67 is stored in the table as 2567 and $1.99 is stored as 199.
Second, it seems that this running total is hard enough to acomplish, but if even possible at all, is there a way to display it with the newest records on top and older ones below it? So it would be a reverse order of the example listed above. The customers table holds the current balance, so you may be able to use this as a starting point to reverse calculate the running balance in this situation.
