Hello,
Is there a way to calculate a running total on an amount field based on a running three day span (SettlmtDt field) in a query?
Then, I want to compare the new total amount to a static limit amount. If it’s over, I want to see the record.
The query has two tables; customer and transaction. They are linked on customerID.
Ex.
Cust1 $500.00 06/01/16 Cust1 Limit = 700.00
Cust1 $250.00 06/02/16
Cust1 $500.00 06/03/16
Cust1 $400.00 06/04/16
Cust1 $500.00 06/05/16
Cust2 $1500.00 06/01/16 Cust2 Limit = 2700.00
Cust2 $1250.00 06/02/16
Cust2 $500.00 06/03/16
Cust2 $1400.00 06/04/16
Cust2 $500.00 06/05/16
Cust3 $3500.00 06/01/16 Cust3 Limit = 5000.00
Cust3 $250.00 06/02/16
Cust3 $3500.00 06/03/16
Cust3 $100.00 06/04/16
Cust3 $500.00 06/05/16
The query would look at days 1+2+3, then 2+3+4, then 3+4+5 etc. and calculate the totals for each of the customers $amts on each of those day groups.
Query Results:
Cust1 $1250.00 06/03/16
Cust1 $1150.00 06/04/16
Cust1 $1400.00 06/05/16
Cust2 $3250.00 06/03/16
Cust2 $3150.00 06/04/16
Cust3 $7250.00 06/03/16
I don't know where I would even start.
Any help is greatly appreciated.
Thank you in advance.
Is there a way to calculate a running total on an amount field based on a running three day span (SettlmtDt field) in a query?
Then, I want to compare the new total amount to a static limit amount. If it’s over, I want to see the record.
The query has two tables; customer and transaction. They are linked on customerID.
Ex.
Cust1 $500.00 06/01/16 Cust1 Limit = 700.00
Cust1 $250.00 06/02/16
Cust1 $500.00 06/03/16
Cust1 $400.00 06/04/16
Cust1 $500.00 06/05/16
Cust2 $1500.00 06/01/16 Cust2 Limit = 2700.00
Cust2 $1250.00 06/02/16
Cust2 $500.00 06/03/16
Cust2 $1400.00 06/04/16
Cust2 $500.00 06/05/16
Cust3 $3500.00 06/01/16 Cust3 Limit = 5000.00
Cust3 $250.00 06/02/16
Cust3 $3500.00 06/03/16
Cust3 $100.00 06/04/16
Cust3 $500.00 06/05/16
The query would look at days 1+2+3, then 2+3+4, then 3+4+5 etc. and calculate the totals for each of the customers $amts on each of those day groups.
Query Results:
Cust1 $1250.00 06/03/16
Cust1 $1150.00 06/04/16
Cust1 $1400.00 06/05/16
Cust2 $3250.00 06/03/16
Cust2 $3150.00 06/04/16
Cust3 $7250.00 06/03/16
I don't know where I would even start.
Any help is greatly appreciated.
Thank you in advance.