I need help building an expression within a query or report.
I have 2 queries I am utilizing to create a new query.
The first query (Paid) houses this information:
Month Sum of Paid
1 100
2 450
3 700
.
.
12 200
Months are from 1-12.
The second query (Balance) houses this information
Month Balance of Account
1 1000
2 1000
3 5000
.
.
12 1000
Months are from 1-12.
I am looking to create a new query utilizing the above queries to create an
expression that will do the following by month
Month Percent
1 month1 balance/month 1 paid
2 sum of month1 and 2 balance/month 2 paid
3 sum of month 1,2 and 3 balance/month 3 paid
.
.
12 sum of months 1-12 balance/month 12 paid
Any help would be appreciated.
John Spencer - 26 Nov 2007 16:39 GMT
The SQL of the query should look something like the following.
SELECT Paid.Month
, (SELECT Sum({Balance Of Account])
FROM Balance
WHERE Balance.Month <= Paid.Month) as RunningBalance
, (SELECT Sum({Balance Of Account])
FROM Balance
WHERE Balance.Month <= Paid.Month) / Paid.[Sum of Paid] as Calculation
FROM Paid

Signature
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
>I need help building an expression within a query or report.
> I have 2 queries I am utilizing to create a new query.
[quoted text clipped - 29 lines]
>
> Any help would be appreciated.
nazzoli - 26 Nov 2007 19:40 GMT
I messed up on my calculation. I need it to divide the Paid by the balance.
[sum of paid]/[balance of account]
> The SQL of the query should look something like the following.
>
[quoted text clipped - 40 lines]
> >
> > Any help would be appreciated.
John Spencer - 26 Nov 2007 19:57 GMT
The SQL then might look like the following
SELECT Balance.Month
, (SELECT Sum([Sum of Paid])
FROM Paid
WHERE Paid.Month <=Balance.Month) /
Balance.[Balance of Account] as Calculation
FROM Balance

Signature
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
>I messed up on my calculation. I need it to divide the Paid by the
>balance.
[quoted text clipped - 47 lines]
>> >
>> > Any help would be appreciated.
nazzoli - 26 Nov 2007 20:37 GMT
The SQL you wrote before but I explained myself backwards. Here is How I
need it written.
Month Percent
1 month 1 paid/ sum of month 1-12 balance
2 month 2 paid/ sum of monht 1-11 balance
3 month 3 paid/ sum of month 1-10 balance
.
.
12 month 12 paid/ month 1 balance
Any help would be appreciated.
Thanks.
> The SQL then might look like the following
>
[quoted text clipped - 56 lines]
> >> >
> >> > Any help would be appreciated.
John Spencer - 26 Nov 2007 20:45 GMT
This is my last try. Good Luck
SELECT Paid.Month
, (SELECT Sum({Balance Of Account])
FROM Balance
WHERE Balance.Month <= 13-Paid.Month) as RunningBalance
, Paid.[Sum of Paid] /
(SELECT Sum({Balance Of Account])
FROM Balance
WHERE Balance.Month <= 13-Paid.Month)as Calculation
FROM Paid

Signature
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
> The SQL you wrote before but I explained myself backwards. Here is How I
> need it written.
[quoted text clipped - 71 lines]
>> >> >
>> >> > Any help would be appreciated.
nazzoli - 26 Nov 2007 21:54 GMT
Perfect
Thank you.
> This is my last try. Good Luck
>
[quoted text clipped - 83 lines]
> >> >> >
> >> >> > Any help would be appreciated.
Dale Fye - 26 Nov 2007 17:14 GMT
How would you use such a statistic?
I don't understand the utility of a statistic that sums all of the previous
months balances divided by the current months payment? I could understand
doing it the other way around, all of the previous months payments, divided
by the current balance.
Dale

Signature
Don''t forget to rate the post if it was helpful!
Email address is not valid.
Please reply to newsgroup only.
> I need help building an expression within a query or report.
> I have 2 queries I am utilizing to create a new query.
[quoted text clipped - 30 lines]
>
>
nazzoli - 26 Nov 2007 18:28 GMT
Actually yes, it should be that way. I mistyped the formula I was looking
for. It should be Payments/Balance.
> How would you use such a statistic?
>
[quoted text clipped - 39 lines]
> >
> >