Better Alternative to Last()

[Gwen H]

Okay, I have posted my question here on two previous occasions, and either
I'm doing a terrible job of explaining myself, or the people who have read my
posts are extremely busy and didn't have the time to read and understand my
post. I also admit that I am SQL ignorant, and probably ask stupid questions
anyway.

I currently have a query that pulls data from a table with one entry per
month, per loan officer. The query sums(totals) some of the fields, and is
supposed to pick the last entry for each loan officer for other fields.
However, as I've learned from other posts in this discussion group, Last()
doesn't work correctly on a consistent basis. So, I need an alternative
method.

The table from which this query pulls data contains a month field. But I'm
not sure how to add the month field to the query to get the results I want.
I'm also not sure if I need to use Dmax() or something else to get the query
to select the most recent month's $ on the fields that are currently using
Last(). Here is the SQL code snippet that I know needs to be modified, I just
don't know how:

Last(Iif(Retail_Manager_Activities.Month BETWEEN #01/01/2005# AND
#03/31/2005#,Retail_Manager_Activities.Loan_Portfoliutstanding,0)) AS
LPO_Qtr1

And here's the complete SQL for the query.

SELECT Retail_Manager_Activities.Retail_Manager,
Last(Iif(Retail_Manager_Activities.Month BETWEEN #01/01/2005# AND
#03/31/2005#,Retail_Manager_Activities.Loan_Portfoliutstanding,0)) AS
LPO_Qtr1, Sum(Retail_Manager_Activities.New_Loans_Funded) AS NLF_Qtr1,
Last(Iif(Retail_Manager_Activities.Month BETWEEN #01/01/2005# AND
#03/31/2005#,Retail_Manager_Activities.Deposits_Outstanding,0)) AS DO_Qtr1,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr1,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr1,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr1,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr1
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)>=#1/1/2005# And
(Retail_Manager_Activities.Month)<=#3/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager;

You can see by looking at this SQL that the table
"Retail_Manager_Activities" is the table from which the query is pulling
data. You can also see which fields are being summed, and which are using
Last(). This query has got to stay as it is. The only thing I need to change
is I need to replace Last() with a more reliable SLQ function. But since I'm
an SQL idiot, I don't know enough about functions to know which one to use or
how to put it into the SQL for my query.

Help me, please!
Gwen H
Obtuse in Okaloosa County, FL

 

[Ofer]

I would create two queries
1.
SELECT Retail_Manager_Activities.Retail_Manager,
Max(Month) as MaxMonth, Sum(Retail_Manager_Activities.New_Loans_Funded) AS
NLF_Qtr1,
Sum(Retail_Manager_Activities.New_Deposits) AS ND_Qtr1,
Sum(Retail_Manager_Activities.Security_Referrals) AS SR_Qtr1,
Sum(Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr1,
Sum(Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr1
FROM Retail_Manager_Activities
WHERE (((Retail_Manager_Activities.Month)>=#1/1/2005# And
(Retail_Manager_Activities.Month)<=#3/31/2005#))
GROUP BY Retail_Manager_Activities.Retail_Manager

2.
Second Query join the Retail_Manager_Activities To the query above, with the
two fields
Retail_Manager=Retail_Manager
MaxMonth=Month
' I would change the month field name because it a reserved name in Access

That way youl get all the sum's from the query together with the last entry
from the table.

 

[Gwen H]

Sorry, the current query is too long and complex for me to split it into two
queries. I need to be able to modify the current query slightly. But thanks
for the suggestion. I appreciate your time and effort.