Subquery

J

Jonas

I want to create a query that calculates the percentage of a total and
multiplies each percentage times a single number that is stored in a
table. Here is what I have so far:


SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

I have tried to repace the number 39,000 with a query but I just get
an error message. Should I just use variables for 39,000 and the
number that results from the subquery? Below is a description of what
I want to achieve.

percantage of total equal to or after 2/1/10 times some number
 
K

KARL DEWEY

Try this --
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
 
J

Jonas

Try this --
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

--
Build a little, test a little.



Jonas said:
I want to create a query that calculates the percentage of a total and
multiplies each percentage times a single number that is stored in a
table.  Here is what I have so far:
Click to expand...
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
Click to expand...
I have tried to repace the number 39,000 with a query but I just get
an error message.  Should I just use variables for 39,000 and the
number that results from the subquery?  Below is a description of what
I want to achieve.
Click to expand...
percantage of total equal to or after 2/1/10 times some number
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hello Karl,

This is what I have and it works fine. However, when I try to replace
39,000 with the statements to obtain 39,000, I get errors. What could
I be doing wrong?
 
K

KARL DEWEY

However, when I try to replace 39,000 with the statements to obtain 39,000,
I get errors.
What 'statements'? Post them. Maybe that is where the error is. Post the
SQL where you include the 'statements'.

--
Build a little, test a little.


Jonas said:
Try this --
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

--
Build a little, test a little.



Jonas said:
I want to create a query that calculates the percentage of a total and
multiplies each percentage times a single number that is stored in a
table. Here is what I have so far:
Click to expand...
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
Click to expand...
I have tried to repace the number 39,000 with a query but I just get
an error message. Should I just use variables for 39,000 and the
number that results from the subquery? Below is a description of what
I want to achieve.
Click to expand...
percantage of total equal to or after 2/1/10 times some number
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hello Karl,

This is what I have and it works fine. However, when I try to replace
39,000 with the statements to obtain 39,000, I get errors. What could
I be doing wrong?
.
Click to expand...
 
J

Jonas

I get errors.
What 'statements'?  Post them.  Maybe that is where the error is.  Post the
SQL where you include the 'statements'.

--
Build a little, test a little.



Jonas said:
Try this --
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
--
Build a little, test a little.
:
I want to create a query that calculates the percentage of a total and
multiplies each percentage times a single number that is stored in a
table.  Here is what I have so far:
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
I have tried to repace the number 39,000 with a query but I just get
an error message.  Should I just use variables for 39,000 and the
number that results from the subquery?  Below is a description ofwhat
I want to achieve.
percantage of total equal to or after 2/1/10 times some number
.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Hello Karl,
Click to expand...
This is what I have and it works fine.  However, when I try to replace
39,000 with the statements to obtain 39,000, I get errors.  What could
I be doing wrong?
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Below is the statement to replace 39,000. The table TrusteeFee only
contains one number. Would it be better to just put than number into
a variable? Should I use a different function such as, AVG, MAX or
MIN. I don't see why this would matter.


SELECT Sum(TrusteeFee.TrstTotalTrustFee) AS SumOfTrstTotalTrustFee
FROM TrusteeFee;
 
K

KARL DEWEY

In your first post you said 'multiplies each percentage times a single number
that is stored in a table.' but you are using a Sum. Which is it a single
record with a number or one field with numbers?

If one record then it is easy --
SELECT AmortSchedule.AmtBorUniqueID,
Sum([TrusteeFee].[TrstTotalTrustFee]*([AmtPrincPayAmount]/(SELECT
Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule, TrusteeFee
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

If you need a sum then it may not work bu try this --
SELECT AmortSchedule.AmtBorUniqueID,
Sum(Sum([TrusteeFee].[TrstTotalTrustFee])*([AmtPrincPayAmount]/(SELECT
Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule, TrusteeFee
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

SumOfTrstTotalTrustFee
If you need a sum you can name the query in your last post as
qryMyTrusteeFee then this --
SELECT AmortSchedule.AmtBorUniqueID,
Sum([qryMyTrusteeFee].[SumOfTrstTotalTrustFee]*([AmtPrincPayAmount]/(SELECT
Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule, qryMyTrusteeFee
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

No promises.

--
Build a little, test a little.


Jonas said:
However, when I try to replace 39,000 with the statements to obtain 39,000,
Click to expand...

I get errors.
What 'statements'? Post them. Maybe that is where the error is. Post the
SQL where you include the 'statements'.

--
Build a little, test a little.



Jonas said:
On Mar 10, 6:31 pm, KARL DEWEY <[email protected]>
wrote:
Try this --
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
Click to expand...
:
I want to create a query that calculates the percentage of a total and
multiplies each percentage times a single number that is stored in a
table. Here is what I have so far:
Click to expand...
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
Click to expand...
I have tried to repace the number 39,000 with a query but I just get
an error message. Should I just use variables for 39,000 and the
number that results from the subquery? Below is a description of what
I want to achieve.
Click to expand...
percantage of total equal to or after 2/1/10 times some number
.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Hello Karl,
Click to expand...
This is what I have and it works fine. However, when I try to replace
39,000 with the statements to obtain 39,000, I get errors. What could
I be doing wrong?
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Below is the statement to replace 39,000. The table TrusteeFee only
contains one number. Would it be better to just put than number into
a variable? Should I use a different function such as, AVG, MAX or
MIN. I don't see why this would matter.


SELECT Sum(TrusteeFee.TrstTotalTrustFee) AS SumOfTrstTotalTrustFee
FROM TrusteeFee;
.
Click to expand...
 
J

Jonas

In your first post you said 'multiplies each percentage times a single number
that is stored in a table.' but you are using a Sum. Which is it a single
record with a number or one field with numbers?

If one record then it is easy --
SELECT AmortSchedule.AmtBorUniqueID,
Sum([TrusteeFee].[TrstTotalTrustFee]*([AmtPrincPayAmount]/(SELECT
Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule, TrusteeFee
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

If you need a sum then it may not work bu try this --
SELECT AmortSchedule.AmtBorUniqueID,
Sum(Sum([TrusteeFee].[TrstTotalTrustFee])*([AmtPrincPayAmount]/(SELECT
Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule, TrusteeFee
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

SumOfTrstTotalTrustFee
If you need a sum you can name the query in your last post as  
qryMyTrusteeFee then this --
SELECT AmortSchedule.AmtBorUniqueID,
Sum([qryMyTrusteeFee].[SumOfTrstTotalTrustFee]*([AmtPrincPayAmount]/(SELECT
Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule, qryMyTrusteeFee
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;

No promises.

--
Build a little, test a little.



Jonas said:
However, when I try to replace 39,000 with the statements to obtain39,000,
I get errors.
What 'statements'?  Post them.  Maybe that is where the error is. Post the
SQL where you include the 'statements'.
--
Build a little, test a little.
:
On Mar 10, 6:31 pm, KARL DEWEY <[email protected]>
wrote:
Try this --
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
--
Build a little, test a little.
:
I want to create a query that calculates the percentage of a total and
multiplies each percentage times a single number that is storedin a
table.  Here is what I have so far:
SELECT AmortSchedule.AmtBorUniqueID, Sum(39000*([AmtPrincPayAmount]/
(SELECT Sum(AmortSchedule.AmtPrincPayAmount) AS SumOfAmtPrincPayAmount
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))))) AS BorTrustFee2
FROM AmortSchedule
WHERE (((AmortSchedule.AmtPayDate)>=#2/1/2010#))
GROUP BY AmortSchedule.AmtBorUniqueID;
I have tried to repace the number 39,000 with a query but I just get
an error message.  Should I just use variables for 39,000 andthe
number that results from the subquery?  Below is a description of what
I want to achieve.
percantage of total equal to or after 2/1/10 times some number
.- Hide quoted text -
- Show quoted text -
Hello Karl,
This is what I have and it works fine.  However, when I try to replace
39,000 with the statements to obtain 39,000, I get errors.  What could
I be doing wrong?
.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Below is the statement to replace 39,000.  The table TrusteeFee only
contains one number.  Would it be better to just put than number into
a variable?  Should I use a different function such as, AVG, MAX or
MIN.  I don't see why this would matter.
Click to expand...
SELECT Sum(TrusteeFee.TrstTotalTrustFee) AS SumOfTrstTotalTrustFee
FROM TrusteeFee;
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The first one worked. Thanks for your help.
 

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

Help with a subquery 5
minimize repeating query criteria & generating monthly YTD totals 1
Running average - subquery? 4
Subquery not integrating to query to display previous sum of quantities 3
Subquery Question 2
Subquery issue 5
Subquery select field from previous record 4
Year to Date subquery 3

Top