Help! Help! Help!

[Guest]

I thought I had this problem solved, and now that I'm running *year-end*
reports I discover it's not solved. I have a query that is not functioning
properly on two of its fields. The query basically pulls fourth-quarter sales
data from a table. For all but two of the fields in the query, the amounts
for October, November, and December are summed. For two of the fields in the
query, I need to always pick the amounts from the last month for which we
have data during the quarter. For example, if I run the query in December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in January, I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two fields
enclosed in the Last() function. These are the ones where I need to pick up
the data of the last month in the quarter for which I have entered data. All
the other fields are summed; only the two enclosed in a Last() function are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the query: On
the two fields enclosed in the Last() function, sort them by date and THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I thought I had
fixed. Right before a year-end deadline, it decides to show me it's not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

 

[Guest]

Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0)) 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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

 

[Randy Harris]

Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0)) 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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

I thought I had this problem solved, and now that I'm running *year-end*
reports I discover it's not solved. I have a query that is not functioning
properly on two of its fields. The query basically pulls fourth-quarter sales
data from a table. For all but two of the fields in the query, the amounts
for October, November, and December are summed. For two of the fields in the
query, I need to always pick the amounts from the last month for which we
have data during the quarter. For example, if I run the query in December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in January, I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two fields
enclosed in the Last() function. These are the ones where I need to pick up
the data of the last month in the quarter for which I have entered data. All
the other fields are summed; only the two enclosed in a Last() function are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the query: On
the two fields enclosed in the Last() function, sort them by date and THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I thought I had
fixed. Right before a year-end deadline, it decides to show me it's not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

Gwen, I don't think Last will give you a predictable selection unless it is
working in an Order By set of records. What about Max, instead of Last?

I was thinking:

IIf(Retail_Manager_Activities.Month Between
#10/1/2005# And #12/31/2005#,
Max([Retail_Manager_Activities].[Loan_Portfolio_Outstanding]),
0)
AS LPO_Qtr4

 

[Dale Fye]

Gwen,

First, Month is an Access reserved word and should not be used as a field
name in your database; instead, use something like SalesMonth. If the
concept of changing a field name and the hassels that creates seems
daunting, check out the product "Speed Ferret". This is an Access search
and replace tool that will identify every occurance of a string in any type
of Access object. It then lets you select which occurances you want to
replace or ignore it. Single copy charge is $199, but you can save that in
the amount of time wasted searching through a single large database.

Second, if your "Month" field contains only date information, and no time
stamps, then you are OK using <=12/31/2005 value, but if that column
contains any reference to time other than 00:00:00 or 12:00:00 AM, then you
need to use <1/1/2006 as the date value in the second part of your criteria.

On to your question. Since you already have a where clause that restricts
the data to the last quarter, I don't think you need to do that again. I
think all you need to do is check whether the date is for the previous
month. So I think you could use:

Sum(iif(Month(Retail_Manager_Activities.Month) = Dateadd("m", -1, Date()),
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding], 0)) as LPT_Qtr4


HTH
Dale

My first question is: Does your [Month] column contain time information as

Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0))
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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

I thought I had this problem solved, and now that I'm running *year-end*
reports I discover it's not solved. I have a query that is not
functioning
properly on two of its fields. The query basically pulls fourth-quarter
sales
data from a table. For all but two of the fields in the query, the
amounts
for October, November, and December are summed. For two of the fields in
the
query, I need to always pick the amounts from the last month for which we
have data during the quarter. For example, if I run the query in
December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in January,
I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two
fields
enclosed in the Last() function. These are the ones where I need to pick
up
the data of the last month in the quarter for which I have entered data.
All
the other fields are summed; only the two enclosed in a Last() function
are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the query:
On
the two fields enclosed in the Last() function, sort them by date and
THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I thought I
had
fixed. Right before a year-end deadline, it decides to show me it's not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

 

[Guest]

Sorry that the field is named "Month", but I didn't setup this database
originally and haven't had time to correct all the mistakes in it. I have a
reporting deadline to meet, and what I really need to focus on right now is
fixing the immediate problem.

Thanks for the date info; I will look at that.

As I indicated in my post, I am not trying to sum the two fields I'm having
a problem with. I need to select the last or most recent month out of a
three-month time period.

GwenH

Gwen,

First, Month is an Access reserved word and should not be used as a field
name in your database; instead, use something like SalesMonth. If the
concept of changing a field name and the hassels that creates seems
daunting, check out the product "Speed Ferret". This is an Access search
and replace tool that will identify every occurance of a string in any type
of Access object. It then lets you select which occurances you want to
replace or ignore it. Single copy charge is $199, but you can save that in
the amount of time wasted searching through a single large database.

Second, if your "Month" field contains only date information, and no time
stamps, then you are OK using <=12/31/2005 value, but if that column
contains any reference to time other than 00:00:00 or 12:00:00 AM, then you
need to use <1/1/2006 as the date value in the second part of your criteria.

On to your question. Since you already have a where clause that restricts
the data to the last quarter, I don't think you need to do that again. I
think all you need to do is check whether the date is for the previous
month. So I think you could use:

Sum(iif(Month(Retail_Manager_Activities.Month) = Dateadd("m", -1, Date()),
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding], 0)) as LPT_Qtr4


HTH
Dale

My first question is: Does your [Month] column contain time information as

Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0))
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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

I thought I had this problem solved, and now that I'm running *year-end*
reports I discover it's not solved. I have a query that is not
functioning
properly on two of its fields. The query basically pulls fourth-quarter
sales
data from a table. For all but two of the fields in the query, the
amounts
for October, November, and December are summed. For two of the fields in
the
query, I need to always pick the amounts from the last month for which we
have data during the quarter. For example, if I run the query in
December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in January,
I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two
fields
enclosed in the Last() function. These are the ones where I need to pick
up
the data of the last month in the quarter for which I have entered data.
All
the other fields are summed; only the two enclosed in a Last() function
are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the query:
On
the two fields enclosed in the Last() function, sort them by date and
THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I thought I
had
fixed. Right before a year-end deadline, it decides to show me it's not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

 

[Guest]

Your suggestion would cause the query to select the highest amount out of the
three months. I actually need to select the amount associated with the most
recent month. In other words, taking tne Loan Portfolio Outstanding (LPO)
field as an example, if I run the query in December I need it to select the
LPO amount for November. If I run the query in January, I need it to select
the LPO amount for December.

I'm thinking I need to do a subquery, but everything I've tried doesn't
work. I may not have the syntax right. I deleted my subquery attempts on
Friday out of sheer frustration, so I can't post what I've tried.

Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0)) 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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

I thought I had this problem solved, and now that I'm running *year-end*
reports I discover it's not solved. I have a query that is not functioning
properly on two of its fields. The query basically pulls fourth-quarter sales
data from a table. For all but two of the fields in the query, the amounts
for October, November, and December are summed. For two of the fields in the
query, I need to always pick the amounts from the last month for which we
have data during the quarter. For example, if I run the query in December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in January, I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two fields
enclosed in the Last() function. These are the ones where I need to pick up
the data of the last month in the quarter for which I have entered data. All
the other fields are summed; only the two enclosed in a Last() function are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the query: On
the two fields enclosed in the Last() function, sort them by date and THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I thought I had
fixed. Right before a year-end deadline, it decides to show me it's not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

Gwen, I don't think Last will give you a predictable selection unless it is
working in an Order By set of records. What about Max, instead of Last?

I was thinking:

IIf(Retail_Manager_Activities.Month Between
#10/1/2005# And #12/31/2005#,
Max([Retail_Manager_Activities].[Loan_Portfolio_Outstanding]),
0)
AS LPO_Qtr4

 

[Dale Fye]

Gwen

As I indicated in my post, I am not trying to sum the two fields I'm
having
a problem with. I need to select the last or most recent month out of a
three-month time period.

Did you try my suggestion, or are you just looking for excuses?

From the sounds of it, you only have one record in the table that will refer
to the previous month, but you never indicated this in any of your other
posts. If this assessment is correct, then the technique mentioned below
will still work, as it will only find one record that is from last month,
and sum it with zeros (0) from the previous two months. Does that make
sense? Try it, you might like the results.

HTH
Dale

Again, I strongly urge you to purchase Speed Ferret (I am in no way
associated with the company, and do not receive kick-backs from them for
recommending them) if you have any intention of "fixing" the errors in your
database. It is well worth the money in the time and frustration it will
save you.

Sorry that the field is named "Month", but I didn't setup this database
originally and haven't had time to correct all the mistakes in it. I have
a
reporting deadline to meet, and what I really need to focus on right now
is
fixing the immediate problem.

Thanks for the date info; I will look at that.

As I indicated in my post, I am not trying to sum the two fields I'm
having
a problem with. I need to select the last or most recent month out of a
three-month time period.

GwenH

Gwen,

First, Month is an Access reserved word and should not be used as a field
name in your database; instead, use something like SalesMonth. If the
concept of changing a field name and the hassels that creates seems
daunting, check out the product "Speed Ferret". This is an Access
search
and replace tool that will identify every occurance of a string in any
type
of Access object. It then lets you select which occurances you want to
replace or ignore it. Single copy charge is $199, but you can save that
in
the amount of time wasted searching through a single large database.

Second, if your "Month" field contains only date information, and no time
stamps, then you are OK using <=12/31/2005 value, but if that column
contains any reference to time other than 00:00:00 or 12:00:00 AM, then
you
need to use <1/1/2006 as the date value in the second part of your
criteria.

On to your question. Since you already have a where clause that
restricts
the data to the last quarter, I don't think you need to do that again. I
think all you need to do is check whether the date is for the previous
month. So I think you could use:

Sum(iif(Month(Retail_Manager_Activities.Month) = Dateadd("m", -1,
Date()),
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding], 0)) as LPT_Qtr4


HTH
Dale

My first question is: Does your [Month] column contain time information
as
message

Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0))
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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;


:

I thought I had this problem solved, and now that I'm running
*year-end*
reports I discover it's not solved. I have a query that is not
functioning
properly on two of its fields. The query basically pulls
fourth-quarter
sales
data from a table. For all but two of the fields in the query, the
amounts
for October, November, and December are summed. For two of the fields
in
the
query, I need to always pick the amounts from the last month for which
we
have data during the quarter. For example, if I run the query in
December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in
January,
I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two
fields
enclosed in the Last() function. These are the ones where I need to
pick
up
the data of the last month in the quarter for which I have entered
data.
All
the other fields are summed; only the two enclosed in a Last()
function
are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the
query:
On
the two fields enclosed in the Last() function, sort them by date and
THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I thought
I
had
fixed. Right before a year-end deadline, it decides to show me it's
not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

 

[Guest]

No, what you're not understanding is that I don't need to sum the fields. I
have 11 records from the previous month that I need to select for the two
fields in question.

Gwen

As I indicated in my post, I am not trying to sum the two fields I'm
having
a problem with. I need to select the last or most recent month out of a
three-month time period.

Did you try my suggestion, or are you just looking for excuses?

From the sounds of it, you only have one record in the table that will refer
to the previous month, but you never indicated this in any of your other
posts. If this assessment is correct, then the technique mentioned below
will still work, as it will only find one record that is from last month,
and sum it with zeros (0) from the previous two months. Does that make
sense? Try it, you might like the results.

HTH
Dale

Again, I strongly urge you to purchase Speed Ferret (I am in no way
associated with the company, and do not receive kick-backs from them for
recommending them) if you have any intention of "fixing" the errors in your
database. It is well worth the money in the time and frustration it will
save you.

Sorry that the field is named "Month", but I didn't setup this database
originally and haven't had time to correct all the mistakes in it. I have
a
reporting deadline to meet, and what I really need to focus on right now
is
fixing the immediate problem.

Thanks for the date info; I will look at that.

As I indicated in my post, I am not trying to sum the two fields I'm
having
a problem with. I need to select the last or most recent month out of a
three-month time period.

GwenH

Gwen,

First, Month is an Access reserved word and should not be used as a field
name in your database; instead, use something like SalesMonth. If the
concept of changing a field name and the hassels that creates seems
daunting, check out the product "Speed Ferret". This is an Access
search
and replace tool that will identify every occurance of a string in any
type
of Access object. It then lets you select which occurances you want to
replace or ignore it. Single copy charge is $199, but you can save that
in
the amount of time wasted searching through a single large database.

Second, if your "Month" field contains only date information, and no time
stamps, then you are OK using <=12/31/2005 value, but if that column
contains any reference to time other than 00:00:00 or 12:00:00 AM, then
you
need to use <1/1/2006 as the date value in the second part of your
criteria.

On to your question. Since you already have a where clause that
restricts
the data to the last quarter, I don't think you need to do that again. I
think all you need to do is check whether the date is for the previous
month. So I think you could use:

Sum(iif(Month(Retail_Manager_Activities.Month) = Dateadd("m", -1,
Date()),
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding], 0)) as LPT_Qtr4


HTH
Dale

My first question is: Does your [Month] column contain time information
as
message
Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0))
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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;


:

I thought I had this problem solved, and now that I'm running
*year-end*
reports I discover it's not solved. I have a query that is not
functioning
properly on two of its fields. The query basically pulls
fourth-quarter
sales
data from a table. For all but two of the fields in the query, the
amounts
for October, November, and December are summed. For two of the fields
in
the
query, I need to always pick the amounts from the last month for which
we
have data during the quarter. For example, if I run the query in
December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in
January,
I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two
fields
enclosed in the Last() function. These are the ones where I need to
pick
up
the data of the last month in the quarter for which I have entered
data.
All
the other fields are summed; only the two enclosed in a Last()
function
are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the
query:
On
the two fields enclosed in the Last() function, sort them by date and
THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I thought
I
had
fixed. Right before a year-end deadline, it decides to show me it's
not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

 

[Dale Fye]

Sorry I couldn't help you more.

Good luck.

No, what you're not understanding is that I don't need to sum the fields.
I
have 11 records from the previous month that I need to select for the two
fields in question.

Gwen

As I indicated in my post, I am not trying to sum the two fields I'm
having
a problem with. I need to select the last or most recent month out of a
three-month time period.

Did you try my suggestion, or are you just looking for excuses?

From the sounds of it, you only have one record in the table that will
refer
to the previous month, but you never indicated this in any of your other
posts. If this assessment is correct, then the technique mentioned below
will still work, as it will only find one record that is from last month,
and sum it with zeros (0) from the previous two months. Does that make
sense? Try it, you might like the results.

HTH
Dale

Again, I strongly urge you to purchase Speed Ferret (I am in no way
associated with the company, and do not receive kick-backs from them for
recommending them) if you have any intention of "fixing" the errors in
your
database. It is well worth the money in the time and frustration it will
save you.

Sorry that the field is named "Month", but I didn't setup this database
originally and haven't had time to correct all the mistakes in it. I
have
a
reporting deadline to meet, and what I really need to focus on right
now
is
fixing the immediate problem.

Thanks for the date info; I will look at that.

As I indicated in my post, I am not trying to sum the two fields I'm
having
a problem with. I need to select the last or most recent month out of a
three-month time period.

GwenH

:

Gwen,

First, Month is an Access reserved word and should not be used as a
field
name in your database; instead, use something like SalesMonth. If the
concept of changing a field name and the hassels that creates seems
daunting, check out the product "Speed Ferret". This is an Access
search
and replace tool that will identify every occurance of a string in any
type
of Access object. It then lets you select which occurances you want
to
replace or ignore it. Single copy charge is $199, but you can save
that
in
the amount of time wasted searching through a single large database.

Second, if your "Month" field contains only date information, and no
time
stamps, then you are OK using <=12/31/2005 value, but if that column
contains any reference to time other than 00:00:00 or 12:00:00 AM,
then
you
need to use <1/1/2006 as the date value in the second part of your
criteria.

On to your question. Since you already have a where clause that
restricts
the data to the last quarter, I don't think you need to do that again.
I
think all you need to do is check whether the date is for the previous
month. So I think you could use:

Sum(iif(Month(Retail_Manager_Activities.Month) = Dateadd("m", -1,
Date()),
[Retail_Manager_Activities].[Loan_Portfolio_Outstanding], 0)) as
LPT_Qtr4


HTH
Dale

My first question is: Does your [Month] column contain time
information
as
message
Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0))
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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;


:

I thought I had this problem solved, and now that I'm running
*year-end*
reports I discover it's not solved. I have a query that is not
functioning
properly on two of its fields. The query basically pulls
fourth-quarter
sales
data from a table. For all but two of the fields in the query, the
amounts
for October, November, and December are summed. For two of the
fields
in
the
query, I need to always pick the amounts from the last month for
which
we
have data during the quarter. For example, if I run the query in
December, we
will only have October and November data, so I need the query to
pick
November data for these two fields only. When I run the query in
January,
I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the
two
fields
enclosed in the Last() function. These are the ones where I need to
pick
up
the data of the last month in the quarter for which I have entered
data.
All
the other fields are summed; only the two enclosed in a Last()
function
are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the
query:
On
the two fields enclosed in the Last() function, sort them by date
and
THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I
thought
I
had
fixed. Right before a year-end deadline, it decides to show me it's
not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

 

[George Nicholson]

From the JetSQL Help Entry on the First, Last Functions:

"..Remarks:...Because records are usually returned in no particular order
(unless the query includes an ORDER BY clause), the records returned by
these functions will be arbitrary."

So, I'd add "ORDER BY Month" if you want Last(Iif(yada Month, LPO,0) to
return the LPO from the largest month value rather than the LPO of whatever
the last record happens to be. ORDER BY determines what the last record will
be (assuming no ties <g>), otherwise the last record will be arbitrary. The
Last function only looks at the last record.

Not that it matters, but since your WHERE clause is handling the "Month
between this and that" condition, it's redundant to have it again in the iif
formula. (There's no way for the iif() to be evaluating any months outside
the WHERE range, so you don't need to specify it). Since that's the case, I
don't think there's any reason for the iif formula at all. You can probably
simplify it to Last(LPO) and Last(DO), once you get the last recored under
control with the appropriate ORDERBY clause.

Good Luck,

--
George Nicholson

Remove 'Junk' from return address.

Your suggestion would cause the query to select the highest amount out of
the
three months. I actually need to select the amount associated with the
most
recent month. In other words, taking tne Loan Portfolio Outstanding (LPO)
field as an example, if I run the query in December I need it to select
the
LPO amount for November. If I run the query in January, I need it to
select
the LPO amount for December.

I'm thinking I need to do a subquery, but everything I've tried doesn't
work. I may not have the syntax right. I deleted my subquery attempts on
Friday out of sheer frustration, so I can't post what I've tried.

Stupid me, I forgot to paste my SQL in. Here it is:

SELECT Retail_Manager_Activities.Retail_Manager,
Last(IIf(Retail_Manager_Activities.Month Between #10/1/2005# And
#12/31/2005#,[Retail_Manager_Activities].[Loan_Portfolio_Outstanding],0)) 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
WHERE (((Retail_Manager_Activities.Month)>=#10/1/2005# And
(Retail_Manager_Activities.Month)<=#12/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;


:

I thought I had this problem solved, and now that I'm running
*year-end*
reports I discover it's not solved. I have a query that is not functioning
properly on two of its fields. The query basically pulls
fourth-quarter sales
data from a table. For all but two of the fields in the query, the amounts
for October, November, and December are summed. For two of the fields
in the
query, I need to always pick the amounts from the last month for
which we
have data during the quarter. For example, if I run the query in December, we
will only have October and November data, so I need the query to pick
November data for these two fields only. When I run the query in January, I
need it to pick December data for these two fields only.

Out of the SQL SELECT statement pasted below, please look at the two fields
enclosed in the Last() function. These are the ones where I need to
pick up
the data of the last month in the quarter for which I have entered
data. All
the other fields are summed; only the two enclosed in a Last()
function are
the "problem" fields. Everything else works the way I want.

My question is this: how can I change my existing code to tell the query: On
the two fields enclosed in the Last() function, sort them by date and THEN
pick the most recent data for the quarter??????

Like I stated at the beginning of my post, this is a problem I
thought I had
fixed. Right before a year-end deadline, it decides to show me it's
not
fixed. Help!!!!!

GwenH
Desperate in Florida USA

Gwen, I don't think Last will give you a predictable selection unless it
is
working in an Order By set of records. What about Max, instead of Last?

I was thinking:

IIf(Retail_Manager_Activities.Month Between
#10/1/2005# And #12/31/2005#,
Max([Retail_Manager_Activities].[Loan_Portfolio_Outstanding]),
0)
AS LPO_Qtr4