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

 

[Melagan]

A starting point - doesn't allow for date criteria as I couldn't get it to work.

You'll probably need to substitute your own field names where code is red

SELECT 
	b.[COLOR=red]CaseNum[/color]
	, b.[COLOR=red]Origination[/color]
	, tblX.SumOfPayAmount
	, Sum([b].[COLOR=red][Origination][/color]-[tblx].[SumOfPayAmount]) AS CurrentBalance

FROM [COLOR=red]tblBalance[/color] AS b 

INNER JOIN [SELECT 
		Sum(p.[COLOR=red]PayAmount[/color]) AS SumOfPayAmount
		, p.[COLOR=red]CaseNum[/color]  FROM [COLOR=red]tblPayments[/color] as p GROUP BY [COLOR=red]CaseNum][/color]. AS tblX ON b.[COLOR=red]CaseNum[/color] = tblX.[COLOR=red]CaseNum[/color]

GROUP BY b.[COLOR=red]CaseNum[/color]
	, b.[COLOR=red]Origination[/color]
	, tblX.SumOfPayAmount;

~Melagan
______
"It's never too late to become what you might have been.

 

[dedren]

Thank you for the response. Something similar to this is actually what I had before, but that was my mistake. I thought establishing the relationship between these two tables would allow me to use a date critera but I was wrong.

That date critera is the problem I have been having and I am not sure why.

 

[dedren]

I was able to find something along the lines of what I am looking to do in thread701-1185226 . I am not sure what he did to creat the filter nor how that would work in my environment, but maybe it can help someone else answer my question.

 

[randy700]

How about....

SELECT tblBalance.CaseNum, Balance - PaymentTotal AS CurBal
FROM tblBalance INNER JOIN
       (SELECT Sum(Payment) AS PaymentTotal, tblBalance.CaseNum
        FROM tblPayments INNER JOIN tblBalance ON
             tblPayments.CaseNum = tblBalance.CaseNum
        WHERE PayDate <= #[i][COLOR=blue]YourDateHere[/color][/i]#
        GROUP BY tblBalance.CaseNum) AS Q ON
     tblBalance.CaseNum = Q.CaseNum

Randy

 

[dedren]

Thank you, that looks very promising but I am not sure what the last part of the statement is trying to do:

GROUP BY tblBalance.CaseNum) AS Q ON
     tblBalance.CaseNum = Q.CaseNum

 

[lespaul]

randy has created an alias (Q) for the SELECT statement in the INNER JOIN clause. It's the same as creating a query:

qryPaymentBalances:
SELECT Sum(Payment) AS PaymentTotal, tblBalance.CaseNum
FROM tblPayments INNER JOIN tblBalance ON
tblPayments.CaseNum = tblBalance.CaseNum
WHERE PayDate <= #YourDateHere#
GROUP BY tblBalance.CaseNum

and then using qryPaymentBalances in the "real" query:

SELECT tblBalance.CaseNum, Balance - PaymentTotal AS CurBal
FROM tblBalance INNER JOIN
qryPaymentBalances AS Q ON
tblBalance.CaseNum = Q.CaseNum

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

 

[dedren]

I went forward and attempted to try that solution. I did manage to get results, but the results are only displayed for payments made Between the dates specified. I think I understand a little of the concept but I can't see how to change it so that it shows the current balance between the dates specified.

SELECT tblBalances.CaseNum, InitBal - PaymentTotal AS CurBal
FROM tblBalances INNER JOIN [SELECT Sum(PaymentAmount) AS PaymentTotal, tblBalances.CaseNum
FROM tblPaymentslst INNER JOIN tblBalances ON tblPaymentslst.CaseNum = tblBalances.CaseNum
WHERE PaymentDate Between #07/01/2006# And #07/31/2006#
GROUP BY tblBalances.CaseNum]. AS Q ON tblBalances.CaseNum = Q.CaseNum;

 

[randy700]

I see 2 errors in your code, although they could be typos if you didn't paste them here. The "internal" SELECT statement should be surrounded by parentheses (), not brackets []. Also, there should be no DOT before the "AS Q" portion.

the results are only displayed for payments made Between the dates specified
I can't see how to change it so that it shows the current balance between the dates specified.
HUH?? What exactly are you expecting to see. Let's use some test data...
If the initial balance is 5000 on 4/1/06 and payments are made on 5/1/06 (200), 6/1/06 (300), 7/1/06 (200), and 8/1/06 (400), what you should get would be 5000 - 600 = 4400 using the BETWEEN clause in your example. Using <= 8/1/06, you should get 5000 - 1100 = 3900.


Randy

 

[dedren]

Thank you! Oddly those errors didn't change anything in this example but I fixed them so they don't show up later.

I am pretty sure you meant 5000-200-300-200=4300 for the example I used, but yes that is exactly the kind of data I am trying to see. Sorry if it came out all confusing.

As a note I would never have <=[Date] because I require the user to enter the start and end date for other functions.

 

[dedren]

Sorry again, I meant to add that I don't see that result. Now with the corrected code I get $4,800.00, using the Between 7/1/06 and 7/31/06 . If I use <=8/1/06 I get the correct answer but I do have cases where there are payments PRIOR to the initial balance date. I don't want to change that fact because of other compliance issues.

 

[randy700]

Can you post your code again, with some actual sample data from your table?


Randy

 

[lespaul]

So you want to display the current balance due no matter what months you are looking at the payments?

 

[dedren]

I want to display what was the current balance during the time frame I choose. Once it is working again I will link it to a report I have.

Posting the code below:

SELECT tblBalances.CaseNum, InitBal - PaymentTotal AS CurBal
FROM tblBalances INNER JOIN (SELECT Sum(PaymentAmount) AS PaymentTotal, tblBalances.CaseNum
FROM tblPaymentslst INNER JOIN tblBalances ON tblPaymentslst.CaseNum = tblBalances.CaseNum
WHERE PaymentDate Between #07/1/2006# And #07/31/2006#
GROUP BY tblBalances.CaseNum) AS Q ON tblBalances.CaseNum = Q.CaseNum;

Below is a piece of my balances table [tblBalances]:

BalanceId   CaseNum	InitBal         InitDate
38          06-0149	$943.30         2/27/2006
49          06-0170	$3,540.00       4/27/2006
60          06-0239	$5,000.00       4/1/2006

Below is a piece of my payments table [tblPaymentlst]:

PaymentId   CaseNum     PaymentAmount    PaymentDate
102	    06-0149	$97.30           2/9/2006
131	    06-0149	$240.00          5/1/2006
148	    06-0149	$240.00          8/4/2006
125	    06-0170	$600.00          4/26/2006
137	    06-0170	$100.00          6/5/2006
139	    06-0170	$100.00          7/3/2006
147	    06-0170	$100.00          8/3/2006
149	    06-0239	$200.00          5/1/2006
150	    06-0239	$300.00          6/1/2006
151	    06-0239	$200.00          7/1/2006
152	    06-0239	$400.00          8/1/2006

 

[lespaul]

thanks, providing sample data really makes things easier but we still need to know what results you want from this data based on what criteria?

 

[randy700]

I don't understand what you mean by the current balance during the time frame you choose. If payments are made PRIOR TO the time frame you choose (Between 7/1/06 and 7/31/06), wouldn't those payments count towards the current balance? Which brings us back to my original suggestion of using <= your end date, which would include ALL payments made up until that point.

This code will give you the BETWEEN information you asked for:

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 [COLOR=red]BETWEEN #7/1/2006# AND #7/31/2006#[/color]
     GROUP BY tblBalance.CaseNum]. AS Q ON tblBalance.CaseNum = Q.CaseNum;

But THIS code will give you a more accurate report:

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 [COLOR=red]<= #7/31/2006#[/color]
     GROUP BY tblBalance.CaseNum]. AS Q ON tblBalance.CaseNum = Q.CaseNum;

In both cases, you can replace the dates with values from text boxes on your form.

Randy

 

[dedren]

Sorry I wasn't sure what was needed to answer the question, here is some more info.

The main function of this is to generate a report for a month at a time. This report displays different counts of different attributes of a case, including the total balance of each of those attributes.

Trick is, if the head of the department doesn't get a chance to run the report until three months later, it should still give him the correct balance for all three of those months no matter how much it has changed since then.

On the opposite end, if a payment was made before the date of the initial balance it shouldn't alter the balance.

 

[dedren]

I have posed this problem to others and no one has been able to come up with a solution. Is this some limit of Access or SQL?

 

[randy700]

OK. Let's work with just one case number from your sample data. I'll select CaseNum 06-170 and the dates will be as you used in the same post -- between 7/1/06 and 7/31/06.

CaseNum 06-170 has an InitBal of 3540.00 (from 4/27/06).
There was a 100.00 payment made between 7/1/06 and 7/31/06 (on 7/3/06).
There were 2 payments made prior to 7/1/06. 600.00 on 4/26/06 and 100.00 on 6/5/06.

Using that data, what result do you expect to obtain from your query?

Randy

 

[dedren]

Here is what I expect:

CaseNum     CurrentBalance
06-170      $3340.00