Simple Query Problem

D

David

Hi there.

I am working on a query of a query...the underlying sub-query has the
following characteristics:

tblProfitability - [DealID], [StartDate], [EndDate] and [DailyProfit]

In tblProfitability there can easily be 15+ date ranges (i.e. start date -->
end date), each of which cooresponds to a different daily profit (as deals
can be changed during their life). The query I am working on is attempting to
rollup (group) tblProfitability by DealID. As part of this rollup, I want to
return the latest [EndDate] (so I use "Max" - which works), as well as the
[DailyProfit] that cooresponds to that specific [EndDate] or, more generally,
the record of which [EndDate] is a part.

I tried to use "Last", as I sorted the tblProfitability by [DealID] and then
by [EndDate], but this seems to return arbitrary profit figures (most of the
time they do not coorespond to the LATEST profit figure).

Can anyone provide some advice/assistance as to how I might be able to do
this? Feel free to ask any questions if my problem requires clarification.

Thanks in advance,

David
 
D

David

Apologies...here it is. I appreciate any help you can provide.

SELECT qryTRS_Profit.DealID, Sum([DailyAccrual]*[AccrualDayCount]) AS
Revenue, Max(qryTRS_Profit.AccrualEnd) AS AccrualEndDeal,
Last(qryTRS_Profit.DailyAccrual) AS DailyAccrualDeal
FROM qryTRS_Profit
GROUP BY qryTRS_Profit.DealID;

Note that I have simplified it (i.e. removing some other fields) to make
things easier for you. In reality, the Query is joined to another Query where
I grab foreign exchange rates, but I thought it would be easier without
including that.

It is the Last(qryTRS_Profit.DailyAccrual) that does not always seem to
return the last (i.e. the record for which Max(AccrualEnd) is AccrualEndDeal)
daily accrual, but sometimes an arbitrary one.

Many thanks,

David

Lynn Trapp said:
I would help if you would post the SQL for your query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



David said:
Hi there.

I am working on a query of a query...the underlying sub-query has the
following characteristics:

tblProfitability - [DealID], [StartDate], [EndDate] and [DailyProfit]

In tblProfitability there can easily be 15+ date ranges (i.e. start
date -->
end date), each of which cooresponds to a different daily profit (as deals
can be changed during their life). The query I am working on is attempting
to
rollup (group) tblProfitability by DealID. As part of this rollup, I want
to
return the latest [EndDate] (so I use "Max" - which works), as well as the
[DailyProfit] that cooresponds to that specific [EndDate] or, more
generally,
the record of which [EndDate] is a part.

I tried to use "Last", as I sorted the tblProfitability by [DealID] and
then
by [EndDate], but this seems to return arbitrary profit figures (most of
the
time they do not coorespond to the LATEST profit figure).

Can anyone provide some advice/assistance as to how I might be able to do
this? Feel free to ask any questions if my problem requires clarification.

Thanks in advance,

David
Click to expand...
Click to expand...
 
L

Lynn Trapp

Ah, the Last() function is probably not doing what you expect it to do. It
return a value from the last record in the result set returned by a query.
Because of the way records are stored in a database that may, or may not, be
the last record entered.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



David said:
Apologies...here it is. I appreciate any help you can provide.

SELECT qryTRS_Profit.DealID, Sum([DailyAccrual]*[AccrualDayCount]) AS
Revenue, Max(qryTRS_Profit.AccrualEnd) AS AccrualEndDeal,
Last(qryTRS_Profit.DailyAccrual) AS DailyAccrualDeal
FROM qryTRS_Profit
GROUP BY qryTRS_Profit.DealID;

Note that I have simplified it (i.e. removing some other fields) to make
things easier for you. In reality, the Query is joined to another Query
where
I grab foreign exchange rates, but I thought it would be easier without
including that.

It is the Last(qryTRS_Profit.DailyAccrual) that does not always seem to
return the last (i.e. the record for which Max(AccrualEnd) is
AccrualEndDeal)
daily accrual, but sometimes an arbitrary one.

Many thanks,

David

Lynn Trapp said:
I would help if you would post the SQL for your query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



David said:
Hi there.

I am working on a query of a query...the underlying sub-query has the
following characteristics:

tblProfitability - [DealID], [StartDate], [EndDate] and [DailyProfit]

In tblProfitability there can easily be 15+ date ranges (i.e. start
date -->
end date), each of which cooresponds to a different daily profit (as
deals
can be changed during their life). The query I am working on is
attempting
to
rollup (group) tblProfitability by DealID. As part of this rollup, I
want
to
return the latest [EndDate] (so I use "Max" - which works), as well as
the
[DailyProfit] that cooresponds to that specific [EndDate] or, more
generally,
the record of which [EndDate] is a part.

I tried to use "Last", as I sorted the tblProfitability by [DealID] and
then
by [EndDate], but this seems to return arbitrary profit figures (most
of
the
time they do not coorespond to the LATEST profit figure).

Can anyone provide some advice/assistance as to how I might be able to
do
this? Feel free to ask any questions if my problem requires
clarification.

Thanks in advance,

David
Click to expand...
Click to expand...
Click to expand...
 
Top