Access 2007
Hello All
I am trying to create a report that will show (1) Starting Loan Balance (2) Activity for a period the user has defined and (3) Ending Loan Balance.
I have a table I'm calling tblLoanDetals that has the Original Principal Balance stored for each loan.
There is a sub table that is storing all the activity details for each loan, this table is called tblRollForward. This table has the date and amount of each activity. So you might imagine a payment was made on the table on 7/4/2015 and more money was borrowed on 1/15/2016. This table would have a date column populated with 7/4/2015 and the Payment column populated with -50000... then on another line, date would be 1/15/16 and another column called "New Issuance" might be 100000.
I then have a query qryRollForwardTotals which sums all the activity by column. So there is a Sum of Payments column and a Sum of New Issuance column.
When my user attempts to generate a report I want them to enter a Starting Date and Ending Date. Then, the database needs to net all the activity up to the starting date against the Original Principal Balance, this will give me a "Starting Balance" that is specific to the period the user has requested. Then the database needs to net all the activity up to the ending date against the Original Principal Balance, this will give an "Ending Balance" that is specific to the requested period.
Right now, my report generates but its basically generating from the aforementioned query and its showing just the "grand sums" of each activity type. I don't know how to allow the user to enter parameters, let alone, perform these sort of cross-column calculations. This is my very first database, thank you in advance for your help!
I've already tried to use an expression and it only "sort of" worked... if there were any blank fields on the tblRollForward then the expression would not calculate.
I also would like to display each loan, even if there was no activity on it, so in that case, just showing the Starting and Ending Balances.
(BTW, I tried uploading the .accdb file but it failed to do so no matter which computer I tried from, I think maybe there is a problem with the site?)