Expression Help

N

nazzoli

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
 
J

John Spencer

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


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
D

Dale Fye

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
 
N

nazzoli

Actually yes, it should be that way. I mistyped the formula I was looking
for. It should be Payments/Balance.
 
N

nazzoli

I messed up on my calculation. I need it to divide the Paid by the balance.

[sum of paid]/[balance of account]

John Spencer said:
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


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nazzoli said:
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.
Click to expand...
Click to expand...
 
J

John Spencer

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

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nazzoli said:
I messed up on my calculation. I need it to divide the Paid by the
balance.

[sum of paid]/[balance of account]

John Spencer said:
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


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nazzoli said:
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.
Click to expand...
Click to expand...
Click to expand...
 
N

nazzoli

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.

John Spencer said:
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

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nazzoli said:
I messed up on my calculation. I need it to divide the Paid by the
balance.

[sum of paid]/[balance of account]

John Spencer said:
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


--
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.
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.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nazzoli said:
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.

John Spencer said:
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

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nazzoli said:
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.

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


--
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.
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.
Click to expand...
Click to expand...
Click to expand...
 
N

nazzoli

Perfect
Thank you.

John Spencer said:
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

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nazzoli said:
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.

John Spencer said:
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

--
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.

[sum of paid]/[balance of account]

:

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


--
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.
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help Build an expression 1
Access Report 4
Use In() Expression as Query criteria with guts from Function 1
Setting values over multiple records in one Query using Loop statement 1
wish there was a CountDistinct() function 6
Columns using month and year 1
Query for multiple due dates 8
How do I add a field to a query and make a calculation? 3

Top