CoolFactor
Technical User
I have two fields with following titles Deposits and Contributions that both store positive amounts. Initially there was an amount that carried over from the previous year. What I need is the differences from the Deposits and Contributions fields against the amount that was carried over from last year. I've built a report with just showing the deposits and contributions of both these fields grouped by Month. In the design view of date field of the query that I built for this report, I included an expression so I can have parameterized date range report. I want to include a text box in a month header that I created that includes differences from the Deposits and Contributions based on the initial amount carried over from the previous year on a monthly basis. Any ideas will be greatly appreciated.
Let me give you a better Idea of what I am looking for though. I created this excel spreadsheet to give you exactly what I want to accomplish in Access.
Example:
12-31-05 Balance Forwarded: $30,000
Date Deposits Contributions
1-1-06 $2000 $1000
1-15-06 $1000 $1000
January Balance: $31,000
Balance Forwarded: $31,000
Date Deposits Contributions
2-1-06 $5000 $2000
2-23-06 $4000 $1000
2-28-06 $3000 $1000
February Balance: $39,000
Balance Forwarded: $39,000
and it continues for each month
As you can see I'm subtracting the Contributions from the
Balance Forwarded and adding the Deposits to it in order to get the Ending Balance for that month. Then that balance is carried over to the next month.
I want to do this exact same thing in an Access report. What I did in Access thus far is made a table with the Date , Deposits, Contributions and other related fields. The initial deposit that I started with in this Access table was
the $30,000 that was forwarded from 12-31-05 Balance Forwarded. I created a parameterized date report grouped by month. When I enter the parameters for the Start Date and End Date for October for example what happens is the text box I created in the Month Header to account for this end of the month balance is just taking the difference between the Deposits and Contributions instead of the initial deposit of $30,000 and the rest of the previous deposits and contributions.
An example of what is happening is this:
Lets just take the excel spreadsheet I used as an example:
I'll pick the month February:
What's happening in the Access Report is that it is giving me the difference between Deposits and Contributions which would give you the dollar amount of $8000. What I need is dollar amount of $39,000 which is the correct ending February Balance.
In this Report I created parameterized query that would give me the deposits and contributions for that month or for several months depending on the parameters that I enter. The text that I created the month header of the report would contain the expression that would produce the calculation that I am looking for.
Let me give you a better Idea of what I am looking for though. I created this excel spreadsheet to give you exactly what I want to accomplish in Access.
Example:
12-31-05 Balance Forwarded: $30,000
Date Deposits Contributions
1-1-06 $2000 $1000
1-15-06 $1000 $1000
January Balance: $31,000
Balance Forwarded: $31,000
Date Deposits Contributions
2-1-06 $5000 $2000
2-23-06 $4000 $1000
2-28-06 $3000 $1000
February Balance: $39,000
Balance Forwarded: $39,000
and it continues for each month
As you can see I'm subtracting the Contributions from the
Balance Forwarded and adding the Deposits to it in order to get the Ending Balance for that month. Then that balance is carried over to the next month.
I want to do this exact same thing in an Access report. What I did in Access thus far is made a table with the Date , Deposits, Contributions and other related fields. The initial deposit that I started with in this Access table was
the $30,000 that was forwarded from 12-31-05 Balance Forwarded. I created a parameterized date report grouped by month. When I enter the parameters for the Start Date and End Date for October for example what happens is the text box I created in the Month Header to account for this end of the month balance is just taking the difference between the Deposits and Contributions instead of the initial deposit of $30,000 and the rest of the previous deposits and contributions.
An example of what is happening is this:
Lets just take the excel spreadsheet I used as an example:
I'll pick the month February:
What's happening in the Access Report is that it is giving me the difference between Deposits and Contributions which would give you the dollar amount of $8000. What I need is dollar amount of $39,000 which is the correct ending February Balance.
In this Report I created parameterized query that would give me the deposits and contributions for that month or for several months depending on the parameters that I enter. The text that I created the month header of the report would contain the expression that would produce the calculation that I am looking for.
