Mixture of Last and Sum

G

Gwen H

I have a query that essentally pulls loan and deposit activity for the most
recent quarter from a table. The query totals some of the activity for the
entire quarter, and is *supposed* to pull the most recent month's numbers for
Deposits Outstanding and Loan Portfolio Outstanding. The query uses Last to
do this. The problem is this: Since there is nothing in the query to tell it
to order the data by month *before* it selects the last month's numbers for
deposits and loans, it doesn't always do this consistently. How can I modify
this query to make sure it always pulls June's loan and deposit portfolio,
assuming I'm running the query in July? I've pasted the SQL below.

Many thanks in advance!

SELECT Assistant_Retail_Manager_Activities.Assistant_Retail_Manager,
Last(Assistant_Retail_Manager_Activities.Deposits_Outstanding) AS DO_Qtr2,
Sum(Assistant_Retail_Manager_Activities.New_Deposits) AS ND_Qtr2,
Last(Assistant_Retail_Manager_Activities.Loan_Portfolio_Outstanding) AS
LPO_Qtr2, Sum(Assistant_Retail_Manager_Activities.New_Loans_Funded) AS
NLF_Qtr2, Sum(Assistant_Retail_Manager_Activities.Security_Referrals) AS
SR_Qtr2, Sum(Assistant_Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr2,
Sum(Assistant_Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr2,
Avg(Assistant_Retail_Manager_Activities.Cross_Sell_Ratio) AS XSR_Qtr2
FROM Assistant_Retail_Manager_Activities
WHERE (((Assistant_Retail_Manager_Activities.Month)>=#4/1/2005# And
(Assistant_Retail_Manager_Activities.Month)<=#6/30/2005#))
GROUP BY Assistant_Retail_Manager_Activities.Assistant_Retail_Manager;
 
M

MGFoster

Gwen said:
I have a query that essentally pulls loan and deposit activity for the most
recent quarter from a table. The query totals some of the activity for the
entire quarter, and is *supposed* to pull the most recent month's numbers for
Deposits Outstanding and Loan Portfolio Outstanding. The query uses Last to
do this. The problem is this: Since there is nothing in the query to tell it
to order the data by month *before* it selects the last month's numbers for
deposits and loans, it doesn't always do this consistently. How can I modify
this query to make sure it always pulls June's loan and deposit portfolio,
assuming I'm running the query in July? I've pasted the SQL below.

Many thanks in advance!

SELECT Assistant_Retail_Manager_Activities.Assistant_Retail_Manager,
Last(Assistant_Retail_Manager_Activities.Deposits_Outstanding) AS DO_Qtr2,
Sum(Assistant_Retail_Manager_Activities.New_Deposits) AS ND_Qtr2,
Last(Assistant_Retail_Manager_Activities.Loan_Portfolio_Outstanding) AS
LPO_Qtr2, Sum(Assistant_Retail_Manager_Activities.New_Loans_Funded) AS
NLF_Qtr2, Sum(Assistant_Retail_Manager_Activities.Security_Referrals) AS
SR_Qtr2, Sum(Assistant_Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr2,
Sum(Assistant_Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr2,
Avg(Assistant_Retail_Manager_Activities.Cross_Sell_Ratio) AS XSR_Qtr2
FROM Assistant_Retail_Manager_Activities
WHERE (((Assistant_Retail_Manager_Activities.Month)>=#4/1/2005# And
(Assistant_Retail_Manager_Activities.Month)<=#6/30/2005#))
GROUP BY Assistant_Retail_Manager_Activities.Assistant_Retail_Manager;
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've delineated the date range of the query as 4/1/2005 to 6/30/2005.
If you just want June's data just delineate the date range as 6/1/2005
to 6/30/2005 ... or am I not understanding your problem?

Use MIN() and MAX(). I only use First() and Last() to avoid putting the
column in the GROUP BY clause. You can use BETWEEN for the date column.


BTW, don't use "Month" as a column name, since it is an Access VBA
function, and, I belive, an SQL reserved word. If you must use it, bind
it w/ square brackets.

SELECT Assistant_Retail_Manager,
Max(Deposits_Outstanding) AS DO_Qtr2,
Sum(New_Deposits) AS ND_Qtr2,
Max(Loan_Portfolio_Outstanding) AS LPO_Qtr2,
Sum(New_Loans_Funded) AS NLF_Qtr2,
Sum(Security_Referrals) AS SR_Qtr2,
Sum(Trust_Referrals) AS TR_Qtr2,
Sum(Mortgage_Closed) AS MC_Qtr2,
Avg(Cross_Sell_Ratio) AS XSR_Qtr2

FROM Assistant_Retail_Manager_Activities

WHERE [Month] BETWEEN #4/1/2005# And #6/30/2005#

GROUP BY Assistant_Retail_Manager

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBQuVOvoechKqOuFEgEQJkVgCfQKDcxOD3XWydBFKdrrQxPC3yEawAlihb
iRd8UoaXKkjs7HjvmdBOj9o=
=Ay2x
-----END PGP SIGNATURE-----
 
G

Gwen H

I've delineated the date range to cover three months because on some of the
data I need to add up the numbers for all three months. On the two fields in
question, I need to select the numbers for June only. On all the other
fields, I still need April, May, and June because those numbers are being
totalled.

The "month" field isn't currently included in the query. If I use Max()
without including the month field in the query, isn't it going to pick the
highest number and not necessarily the data for June?

MGFoster said:
Gwen said:
I have a query that essentally pulls loan and deposit activity for the most
recent quarter from a table. The query totals some of the activity for the
entire quarter, and is *supposed* to pull the most recent month's numbers for
Deposits Outstanding and Loan Portfolio Outstanding. The query uses Last to
do this. The problem is this: Since there is nothing in the query to tell it
to order the data by month *before* it selects the last month's numbers for
deposits and loans, it doesn't always do this consistently. How can I modify
this query to make sure it always pulls June's loan and deposit portfolio,
assuming I'm running the query in July? I've pasted the SQL below.

Many thanks in advance!

SELECT Assistant_Retail_Manager_Activities.Assistant_Retail_Manager,
Last(Assistant_Retail_Manager_Activities.Deposits_Outstanding) AS DO_Qtr2,
Sum(Assistant_Retail_Manager_Activities.New_Deposits) AS ND_Qtr2,
Last(Assistant_Retail_Manager_Activities.Loan_Portfolio_Outstanding) AS
LPO_Qtr2, Sum(Assistant_Retail_Manager_Activities.New_Loans_Funded) AS
NLF_Qtr2, Sum(Assistant_Retail_Manager_Activities.Security_Referrals) AS
SR_Qtr2, Sum(Assistant_Retail_Manager_Activities.Trust_Referrals) AS TR_Qtr2,
Sum(Assistant_Retail_Manager_Activities.Mortgage_Closed) AS MC_Qtr2,
Avg(Assistant_Retail_Manager_Activities.Cross_Sell_Ratio) AS XSR_Qtr2
FROM Assistant_Retail_Manager_Activities
WHERE (((Assistant_Retail_Manager_Activities.Month)>=#4/1/2005# And
(Assistant_Retail_Manager_Activities.Month)<=#6/30/2005#))
GROUP BY Assistant_Retail_Manager_Activities.Assistant_Retail_Manager;
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've delineated the date range of the query as 4/1/2005 to 6/30/2005.
If you just want June's data just delineate the date range as 6/1/2005
to 6/30/2005 ... or am I not understanding your problem?

Use MIN() and MAX(). I only use First() and Last() to avoid putting the
column in the GROUP BY clause. You can use BETWEEN for the date column.


BTW, don't use "Month" as a column name, since it is an Access VBA
function, and, I belive, an SQL reserved word. If you must use it, bind
it w/ square brackets.

SELECT Assistant_Retail_Manager,
Max(Deposits_Outstanding) AS DO_Qtr2,
Sum(New_Deposits) AS ND_Qtr2,
Max(Loan_Portfolio_Outstanding) AS LPO_Qtr2,
Sum(New_Loans_Funded) AS NLF_Qtr2,
Sum(Security_Referrals) AS SR_Qtr2,
Sum(Trust_Referrals) AS TR_Qtr2,
Sum(Mortgage_Closed) AS MC_Qtr2,
Avg(Cross_Sell_Ratio) AS XSR_Qtr2

FROM Assistant_Retail_Manager_Activities

WHERE [Month] BETWEEN #4/1/2005# And #6/30/2005#

GROUP BY Assistant_Retail_Manager

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBQuVOvoechKqOuFEgEQJkVgCfQKDcxOD3XWydBFKdrrQxPC3yEawAlihb
iRd8UoaXKkjs7HjvmdBOj9o=
=Ay2x
-----END PGP SIGNATURE-----
Click to expand...
 
M

MGFoster

Gwen said:
I've delineated the date range to cover three months because on some of the
data I need to add up the numbers for all three months. On the two fields in
question, I need to select the numbers for June only. On all the other
fields, I still need April, May, and June because those numbers are being
totalled.

The "month" field isn't currently included in the query. If I use Max()
without including the month field in the query, isn't it going to pick the
highest number and not necessarily the data for June?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes. I didn't know what data type the columns "Deposits_Outstanding"
and "Loan_Portfolio_Outstanding" were. I thought they were dates.

If you want to highlight specific data in a resultset that contains
other data you have to use something like this:

SELECT
SUM(IIf(date_column BETWEEN #1/1/2005# And #1/31/2005,PaymentAmt)) As
JanPayment,
SUM(IIf(date_column BETWEEN #2/1/2005# And #2/28/2005,PaymentAmt)) As
FebPayment,
.... etc. ...
FROM ...

You may wish to have 2 queries: one for all 3 months and another for
just a specific month. If you want all the data combined in one object
use a Report - make a sub report for one of the queries and use the main
report for the other.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQuZ9ioechKqOuFEgEQKIhwCcDQWnmw4XvMJRqGgHxkvYyF8YNEwAoJbb
upMzLRrhQ/iDk54qGNZ8e0S5
=NSf5
-----END PGP SIGNATURE-----
 
G

Gwen H

Actually, the fields that are being summed are working just fine, and I
didn't really need help with those. What I need help with is the two fields
that are using "last" instead of "sum"- Deposits Outstanding and Loan
Portfolio Outstanding.

Our aim is to have the query select the values from the last month in the
quarter (in the second quarter of 2005, that would be June). However, it's
not doing that. It's actually picking up the last values ***listed*** in the
result set. It correctly examines the values for April, May, and June, but
doesn't always select the values for June. Instead, it's selecting the values
for whichever of these three records (or rows) comes last in the table on
which the query is based. Since we don't always keep the table's records in
order by month, the query doesn't always work correctly.

Do I need to just create a query that basically duplicates the table, but
puts everything in order by month? And then base my "problem child" query on
that instead of the table itself?

Thank you - and I'm sorry my original post wasn't as clear as it should have
been.
 
G

Gwen H

Never mind ... duh! ... You answered my question with your last post. I was
just too sleepy (or dense) to see that.

Thanks so much for your help. After a *really* read your last post, your
solution worked great!
 

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

Combine Related Queries ??? 2
Please Fix This Query 1

Top