Urgent! Subquery help needed!

[Guest]

I have a looming report deadline, and suddenly a query I thought I had fixed
shows me it's not fixed. In the SQL below, please please PLEASE look at the
subquery and tell me how I can fix this. When I try to run the query, I get
the error message that I cannot I have an aggregate function in the Where
clause of the subquery.

Please understand that while all the other fields are being summed, what I'm
really trying to do with the field in the subquery is select the Loan
Portfolio Outstanding (LPO) amount for the *most recent month*. In other
words, if I run the query in December, I want it to give me the LPO amount
for November. I *do not* want it to sum the LPO amounts for October and
November.

Thanks!
GwenH

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
Max([Retail_Manager_Activities].[Month]))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[OfficeDev18 via AccessMonster.com]

Hi, Gwen,

I'll assume you copied and pasted your SQL exactly as is. If that's the case,
allow me to make some revisions. Here's the new SQL:

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
(SELECT
Max([Retail_Manager_Activities].[Month]) As MaxOfMonth FROM
[Retail_Manager_Activities] WHERE Retail_Manager_Activities.Retail_Manager =
Retail_Manager))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month) Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

I actually made 2 changes: the first change was to change your first WHERE
clause to a sub-query that would compare a number to a number, not an
aggregate number. BTW, if this doesn't work, try changing the
WHERE [Retail_Manager_Activities].[Month] = (SELECT ... slightly to read
WHERE [Retail_Manager_Activities].[Month] In (SELECT ...

In addition, there was a run-on word (I mean 2 words with no space between)
near the end of the query, to wit: WHERE (((Retail_Manager_Activities.Month)
Between ... There's no space between "Month)Between"

Like I said, I assumed you did an exact copy and paste....

HTH

I have a looming report deadline, and suddenly a query I thought I had fixed
shows me it's not fixed. In the SQL below, please please PLEASE look at the
subquery and tell me how I can fix this. When I try to run the query, I get
the error message that I cannot I have an aggregate function in the Where
clause of the subquery.

Please understand that while all the other fields are being summed, what I'm
really trying to do with the field in the subquery is select the Loan
Portfolio Outstanding (LPO) amount for the *most recent month*. In other
words, if I run the query in December, I want it to give me the LPO amount
for November. I *do not* want it to sum the LPO amounts for October and
November.

Thanks!
GwenH

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
Max([Retail_Manager_Activities].[Month]))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[Guest]

Yes, I copied and pasted my SQL exactly as is, but in the original SQL there
was not a run-on word at the end. Go figure!

Based on your suggestion, I modified my SQL as I have pasted below. Now I'm
getting this error message: You tried to execute a query that does not
include the specified expression "Retail_Manager_Activities.Retail_Manager"
as part of an aggregate function.

Huh?

Hi, Gwen,

I'll assume you copied and pasted your SQL exactly as is. If that's the case,
allow me to make some revisions. Here's the new SQL:

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
(SELECT
Max([Retail_Manager_Activities].[Month]) As MaxOfMonth FROM
[Retail_Manager_Activities] WHERE Retail_Manager_Activities.Retail_Manager =
Retail_Manager))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month) Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

I actually made 2 changes: the first change was to change your first WHERE
clause to a sub-query that would compare a number to a number, not an
aggregate number. BTW, if this doesn't work, try changing the
WHERE [Retail_Manager_Activities].[Month] = (SELECT ... slightly to read
WHERE [Retail_Manager_Activities].[Month] In (SELECT ...

In addition, there was a run-on word (I mean 2 words with no space between)
near the end of the query, to wit: WHERE (((Retail_Manager_Activities.Month)
Between ... There's no space between "Month)Between"

Like I said, I assumed you did an exact copy and paste....

HTH

I have a looming report deadline, and suddenly a query I thought I had fixed
shows me it's not fixed. In the SQL below, please please PLEASE look at the
subquery and tell me how I can fix this. When I try to run the query, I get
the error message that I cannot I have an aggregate function in the Where
clause of the subquery.

Please understand that while all the other fields are being summed, what I'm
really trying to do with the field in the subquery is select the Loan
Portfolio Outstanding (LPO) amount for the *most recent month*. In other
words, if I run the query in December, I want it to give me the LPO amount
for November. I *do not* want it to sum the LPO amounts for October and
November.

Thanks!
GwenH

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
Max([Retail_Manager_Activities].[Month]))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[Guest]

I posted my last message in haste ... I am just so desparate to get this
resolved. I have a deadline I've gotta meet.

Anyway, I neglected to mention that your original suggestion did not work,
so I tried creating a separate query that will select each manager's name and
the last month for which there is an entry for that manager in the activities
table. Then I joined that query to my original query, and attempted to write
a subquery that would use the new query. Here's the revised SQL.

SELECT Retail_Manager_Activities.Retail_Manager, (SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
[RM_MaxMonth].[MaxMonth]) AS LPO_Qtr4,
Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,Retail_Manager_Activities.Deposits_Outstanding,0)) AS DO_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities INNER JOIN RM_MaxMonth ON
Retail_Manager_Activities.Retail_Manager = RM_MaxMonth.Retail_Manager
WHERE (((Retail_Manager_Activities.Month) Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

Thanks for your help.
Gwen H

I have a looming report deadline, and suddenly a query I thought I had fixed
shows me it's not fixed. In the SQL below, please please PLEASE look at the
subquery and tell me how I can fix this. When I try to run the query, I get
the error message that I cannot I have an aggregate function in the Where
clause of the subquery.

Please understand that while all the other fields are being summed, what I'm
really trying to do with the field in the subquery is select the Loan
Portfolio Outstanding (LPO) amount for the *most recent month*. In other
words, if I run the query in December, I want it to give me the LPO amount
for November. I *do not* want it to sum the LPO amounts for October and
November.

Thanks!
GwenH

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
Max([Retail_Manager_Activities].[Month]))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[Randy Harris]

Gwen, what kind of field is [Month]? Is that a date field? Assuming so,
and you want the LPO from last month (that seems to be what you mean by
"most recent month"), how about

SELECT ...,...,..., (SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities]
WHERE datepart("m",[Retail_Manager_Activities].[Month]) =

datepart("m",dateadd("m",-1,date))) As LPO_Qtr4, Sum.....

 

[Guest]

Sorry, that didn't work. It's giving me an error message that "At most, one
record can be returned from this query." But thanks so much for trying!!

Gwen, what kind of field is [Month]? Is that a date field? Assuming so,
and you want the LPO from last month (that seems to be what you mean by
"most recent month"), how about

SELECT ...,...,..., (SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities]
WHERE datepart("m",[Retail_Manager_Activities].[Month]) =

datepart("m",dateadd("m",-1,date))) As LPO_Qtr4, Sum.....


--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

I have a looming report deadline, and suddenly a query I thought I had fixed
shows me it's not fixed. In the SQL below, please please PLEASE look at the
subquery and tell me how I can fix this. When I try to run the query, I get
the error message that I cannot I have an aggregate function in the Where
clause of the subquery.

Please understand that while all the other fields are being summed, what I'm
really trying to do with the field in the subquery is select the Loan
Portfolio Outstanding (LPO) amount for the *most recent month*. In other
words, if I run the query in December, I want it to give me the LPO amount
for November. I *do not* want it to sum the LPO amounts for October and
November.

Thanks!
GwenH

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
Max([Retail_Manager_Activities].[Month]))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[Randy Harris]

Apparently you have more than one record in Retail_Manager_Activities where
the Month is equal to last month. Which one do you want the LPO from? This
is pretty difficult without knowing anything about your data.

Sorry, that didn't work. It's giving me an error message that "At most, one
record can be returned from this query." But thanks so much for trying!!

Gwen, what kind of field is [Month]? Is that a date field? Assuming so,
and you want the LPO from last month (that seems to be what you mean by
"most recent month"), how about

SELECT ...,...,..., (SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities]
WHERE datepart("m",[Retail_Manager_Activities].[Month]) =

datepart("m",dateadd("m",-1,date))) As LPO_Qtr4, Sum.....


--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

I have a looming report deadline, and suddenly a query I thought I had fixed
shows me it's not fixed. In the SQL below, please please PLEASE look

at
the

subquery and tell me how I can fix this. When I try to run the query,

I
get

the error message that I cannot I have an aggregate function in the Where
clause of the subquery.

Please understand that while all the other fields are being summed,

what
I'm

really trying to do with the field in the subquery is select the Loan
Portfolio Outstanding (LPO) amount for the *most recent month*. In other
words, if I run the query in December, I want it to give me the LPO amount
for November. I *do not* want it to sum the LPO amounts for October and
November.

Thanks!
GwenH

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
Max([Retail_Manager_Activities].[Month]))
AS LPO_Qtr4, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[OfficeDev18 via AccessMonster.com]

Sorry, but I wasn't available for the last couple of days.

I would have also suggested a seperate subquery; great minds think alike.

The lower query would have the following SQL:

SELECT Retail_Manager, Max(month) as MaxOfMonth FROM
Retail_Manager_Activities GROUP BY Retail_Manager;
maybe call that query qryActMon

Now you can change your original SQL to:

SELECT Retail_Manager_Activities.Retail_Manager, Sum(SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] INNER JOIN qryActMon ON Retail_Manager_Activities.
Retail_Manager = qryActMon.Retail_Manager AS LPO_Qtr4, Sum
(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)Between #10/1/2005# And
#12/31/2005#)) AND ([Retail_Manager_Activities].[Month] = qryActMon.
MaxOfMonth)
GROUP BY Retail_Manager_Activities.Retail_Manager;

HTH

I posted my last message in haste ... I am just so desparate to get this
resolved. I have a deadline I've gotta meet.

Anyway, I neglected to mention that your original suggestion did not work,
so I tried creating a separate query that will select each manager's name and
the last month for which there is an entry for that manager in the activities
table. Then I joined that query to my original query, and attempted to write
a subquery that would use the new query. Here's the revised SQL.

SELECT Retail_Manager_Activities.Retail_Manager, (SELECT
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding] FROM
[Retail_Manager_Activities] WHERE [Retail_Manager_Activities].[Month] =
[RM_MaxMonth].[MaxMonth]) AS LPO_Qtr4,
Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr4,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,Retail_Manager_Activities.Deposits_Outstanding,0)) AS DO_Qtr4,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr4,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr4,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr4,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr4
FROM Retail_Manager_Activities INNER JOIN RM_MaxMonth ON
Retail_Manager_Activities.Retail_Manager = RM_MaxMonth.Retail_Manager
WHERE (((Retail_Manager_Activities.Month) Between #10/1/2005# And
#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

Thanks for your help.
Gwen H

I have a looming report deadline, and suddenly a query I thought I had fixed
shows me it's not fixed. In the SQL below, please please PLEASE look at the

[quoted text clipped - 25 lines]

#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[OfficeDev18 via AccessMonster.com]

Hi, Gwen,

I posted an update to the solution, but apparently you didn't see it yet.
Please see my post of 1-11-06 12:45

Apparently you have more than one record in Retail_Manager_Activities where
the Month is equal to last month. Which one do you want the LPO from? This
is pretty difficult without knowing anything about your data.

Sorry, that didn't work. It's giving me an error message that "At most, one
record can be returned from this query." But thanks so much for trying!!

[quoted text clipped - 43 lines]

 

[Tom Ellison]

Just a tip to posters. The date and time of a post is displayed relative to
each recipient's own time zone. Your post of 1-11-06 12:45 is different in
my time zone. There is a linked identity for each post, but that is not
implemented for click and play, at least not in my OE. I don't have a
suggestion how to specify a post except if you include your time zone of
reference.

Tom Ellison

Hi, Gwen,

I posted an update to the solution, but apparently you didn't see it yet.
Please see my post of 1-11-06 12:45

Apparently you have more than one record in Retail_Manager_Activities
where
the Month is equal to last month. Which one do you want the LPO from?
This
is pretty difficult without knowing anything about your data.

Sorry, that didn't work. It's giving me an error message that "At most,
one
record can be returned from this query." But thanks so much for trying!!

[quoted text clipped - 43 lines]

#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;