Better Alternative to Last()

[Guest]

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_Portfolio_Outstanding,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_Portfolio_Outstanding,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!
Gwen H

 

[[MVP] S.Clark]

Since you are using Last, and most people use it wrong, please read the
following first:

In an Access database, you can find the first or last record in the result
set returned by a query by clicking First or Last in the Total row in the
query design grid. However, the First and Last functions may appear to
return arbitrary records because they don't take into account indexes or
sort orders. Records are returned in chronological order- the order in which
they were entered into the table. This isn't necessarily the same as
alphabetical order, so these functions may or may not return the first or
last record alphabetically. You can display the first or last record in a
query by organizing the data in a predictable sequence and then selecting
the record to be displayed.

Based on this, you may need to use the Min or Max function instead. This
will find a Max/Min value for the column, regardless of chrono or sort
order.

BTW, I answered one of your posts previously, and never determined if the
outer join solved your problem, as you didn't respond.


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[david epsom dot com dot au]

sort orders. Records are returned in chronological order- the order they

were entered into the table. This isn't necessarily the same as

When you compact a database, tables are re-sorted to primary key
order. If you are using an autonumber field as primary key, this
does not change the physical order. For compatibility with Access
1.0, this behaviour is controlled by a registry entry.

When you use a subquery, First and Last sometimes return different
records, because Access sometimes re-orders records in a subquery.
The values you see when you open a select query may not be the
same as the values you get when you use that select query as
a query source.

(david)

 

[Guest]

1. Sorry I didn't respond to your previous post; I think that was the one
where I went "huh?!". In an effort to disguise my ignorance, I chose to
remain silent. It's not that I didn't appreciate your help - just that I
feared I was painfully ignorant and beyond help!

2. Will Max(), when applied to the Month field, return the most recent month?

3. I have tried using Max() with the month field, but I can't seem to get
the syntax right. This doesn't work:

Iif(Max(Assistant_Retail_Manager_Activities.Month) BETWEEN #01/01/2005# AND
#03/31/2005#,Assistant_Retail_Manager_Activities.Deposits_Outstanding,0) AS
DO_Qtr1

I know I've been looking and this too long, and I'm missing the obvious
(plus making myself look stupid).

Gwen H

 

[Guest]

Yes, I gathered that from reading other posts on Last(). When I go into the
table on which the query is based and sort or filter the data, then the query
doesn't work properly with Last() - even if I put the data back in its
original form. I can't stop sorting and filtering the table, so I have to
find a way to make the query work properly 100% of the time.

Thanks for your help.

 

[david epsom dot com dot au]

What exactly is in the month field?

the syntax right. This doesn't work:

What does it return?

 

[Guest]

The month field is a date field, and contains a date such as 7/29/2005. I am
trying to tell the query to select records within a three-month period of
time, select the most recent month from amongst those three records, and then
include an entirely different field from the same record in the query results.

 

[david epsom dot com dot au]

time, select the most recent month from amongst those three records, and

include an entirely different field from the same record in the query

You will have to use two queries: one to select the record, one to
select the other fields associated with that record. You can try
doing it all using a correlated subquery inside the main query, but
I don't think that is robust either.

(david)