Monthly Report 2

[dedren]

I thought I had solved this problem but I was terribly wrong, so I hope someone can help.

I have two tables, [Balance] and [Payments]
1. For every record I have in [Balance] I could have an infinite number in [Payments]
2. Both tables use [CaseNum] to be linked to one another.
3. Both tables also have a date field ([BalDate] & [PayDate])

I want to use these date fields to calculate the current balance (CurBal) between any two dates.

I know how to do this in Excel but for the life of me can't figure it out in Access. In Excel it would be something like:

=B2-SUM(C2:F2)

Where B2 is [Balance] and C2 through F2 are [Payments].

Please... Help Me

 

[lespaul]

and how did you get to that number?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[randy700]

I don't understand your logic, but this code should do the trick for you.

SELECT tblBalance.CaseNum, InitBal - PaymentTotal AS CurBal
FROM tblBalance INNER JOIN [SELECT Sum(PaymentAmount) AS PaymentTotal, tblBalance.CaseNum
     FROM tblPayments INNER JOIN tblBalance ON
               tblPayments.CaseNum = tblBalance.CaseNum
     WHERE PaymentDate BETWEEN InitBal AND [b][COLOR=red]YourEndDate[/color][/b]
     GROUP BY tblBalance.CaseNum]. AS Q ON tblBalance.CaseNum = Q.CaseNum;

Randy

 

[dedren]

4/27/06 - $3540 (This was found to be the initial balance on this date)

4/26/06 - $600 (Payment that occured prior to the initial balance date so this does not reduce the balance)

6/5/06 - $100 (Payment that occured before the 7/31 date so it reduces the balance)

7/3/06 - $100 (Payment that occured before 7/31 date so it reduces the balance)

So the calculation should be: 3540-200=3340

 

[randy700]

Sorry. That last code example should be:

SELECT tblBalance.CaseNum, InitBal - PaymentTotal AS CurBal
FROM tblBalance INNER JOIN 
    [SELECT Sum(PaymentAmount) AS PaymentTotal, tblBalance.CaseNum
     FROM tblPayments INNER JOIN tblBalance ON
               tblPayments.CaseNum = tblBalance.CaseNum
     WHERE PaymentDate BETWEEN [b][COLOR=red]InitDate AND YourEndDate[/color][/b]
     GROUP BY tblBalance.CaseNum]. AS Q 
ON tblBalance.CaseNum = Q.CaseNum;

Randy

 

[dedren]

This gives me results if there are payments made, but if there is no payment made I get an empty result.

Alternatively, is there another way you could suggest to handle this:

I created a database to store information on cases that my department handles. Every month my boss has to present a report to our board on the various status of our cases. I tried to create a report on Access that takes a count of the different case status' and shows the outstanding balance of each for that month. In the database I created forms which allow users to enter the starting balance of a case and its date, plus it allows them to enter payments as they come in and their dates.

My report uses a dozen queries to generate the numbers and balances for each of the differet status in a case.

 

[randy700]

How about...

SELECT tblBalance.CaseNum, [B][COLOR=RED]iif(IsNull(InitBal - PaymentTotal), InitBal, [/color][/b]InitBal - PaymentTotal AS CurBal
FROM tblBalance [b][COLOR=red]LEFT[/COLOR][/B] JOIN 
    [SELECT Sum(PaymentAmount) AS PaymentTotal, tblBalance.CaseNum
     FROM tblPayments INNER JOIN tblBalance ON
               tblPayments.CaseNum = tblBalance.CaseNum
     WHERE PaymentDate BETWEEN InitDate AND YourEndDate
     GROUP BY tblBalance.CaseNum]. AS Q 
ON tblBalance.CaseNum = Q.CaseNum;

Randy

 

[lespaul]

missing a paren in there somewhere randy.....
right before the AS CurBal you need another )

 

[randy700]

Good catch Leslie... thank you.

SELECT tblBalance.CaseNum, iif(IsNull(InitBal - PaymentTotal), InitBal, InitBal - PaymentTotal) AS CurBal
FROM tblBalance LEFT JOIN 
    [SELECT Sum(PaymentAmount) AS PaymentTotal, tblBalance.CaseNum
     FROM tblPayments INNER JOIN tblBalance ON
               tblPayments.CaseNum = tblBalance.CaseNum
     WHERE PaymentDate BETWEEN InitDate AND YourEndDate
     GROUP BY tblBalance.CaseNum]. AS Q 
ON tblBalance.CaseNum = Q.CaseNum;

Randy

 

[dedren]

Sorry, I am still on vacation from work till 9/5/06 so I will try this all out then!