I am struggling to get this query to work. I can get the answers I need if I dump everything into excel but I am trying to get it done via Access so other users can get what they need with one click. I've attached a Test db with samples of the two tables being used. tbl_CustData contains all of the account numbers for each customer along with the balance and payment date. tbl_Accounts just groups the various account numbers into the names I want to use (there are other account numbers on tbl_CustData but I only care about the ones listed in tbl_Accounts).
I want to be able to input a Date and pull back the balances for each customer as of that date. So far I haven't been able to get what I wanted because if I say >= payment date then I also get older balances that I don't want.
The other issue I was having was to have the output use the Account Groups as the headers and for each customer sum all of the balances that go under that group. Below are samples of two outputs, one running it using 4/30/2020 as the date and the other using 5/31/2020.
Let me know if you have any thoughts on how I can get this data or if you need any other information from me. Thank you for your time.
I want to be able to input a Date and pull back the balances for each customer as of that date. So far I haven't been able to get what I wanted because if I say >= payment date then I also get older balances that I don't want.
The other issue I was having was to have the output use the Account Groups as the headers and for each customer sum all of the balances that go under that group. Below are samples of two outputs, one running it using 4/30/2020 as the date and the other using 5/31/2020.
Let me know if you have any thoughts on how I can get this data or if you need any other information from me. Thank you for your time.
As of | 4/30/2020 | ||
CUST_NO | EXPENSE | PRINCIPAL | INTEREST |
111 | -38345.86 | -211653.23 | -46858.29 |
555 | -440 | -163432.2 | -30112.94 |
As of | 5/31/2020 | ||
CUST_NO | EXPENSE | PRINCIPAL | INTEREST |
111 | -39111.63 | -211653.23 | -46858.29 |
555 | -455 | -163432.2 | -30112.94 |