Last vs Max

[paii, Ron]

I have a sales table that logs sales information for each active job at the
end of each month. My question is, will the following query give me the last
sell price for each job in the sales table?

SELECT DISTINCTROW tblJobSales.Job , Last(tblJobSale s.SellPrice) AS SellPrice
FROM tblJobSales WHERE (((tblJobSales. SellDate) Between #1/1/2007# And
#12/31/2007#)) GROUP BY tblJobSales.Job ;

I have been using Max(tblJobSales .SellPrice), but I can have jobs where the
sell price goes down from one month to next.
Jobs that were completed in February would not have a record in March.

[Tom van Stiphout]

On Mon, 19 Mar 2007 16:21:05 -0500, "paii, Ron" <pa**@packairin c.com>
wrote:

Not necessarily.
The way I understand Last, it depends on the sort order. If (unlikely)
we were sorted by SellDate DESC, this query would retrieve the first
amount. Better be explicit.

Alternatively (and to me more reliably) you'd get the max SellDate per
Job in another query, and then join that with the JobSales table,
joining on both fields, to get some fields such as SellPrice for the
max date per job.

-Tom.

>I have a sales table that logs sales information for each active job at the
end of each month. My question is, will the following query give me the last
sell price for each job in the sales table?

SELECT DISTINCTROW tblJobSales.Job , Last(tblJobSale s.SellPrice) AS SellPrice
FROM tblJobSales WHERE (((tblJobSales. SellDate) Between #1/1/2007# And
#12/31/2007#)) GROUP BY tblJobSales.Job ;

I have been using Max(tblJobSales .SellPrice), but I can have jobs where the
sell price goes down from one month to next.
Jobs that were completed in February would not have a record in March.

[paii, Ron]

Thanks for your response Tom.

I was thinking I would need a 2nd query sorted by date, but was hoping the
WHERE clause would have the same affect. This query actually worked on my
current data, but I was concerned that it would fail when more records were
added.

"Tom van Stiphout" <no************ *@cox.netwrote in message
news:g4******** *************** *********@4ax.c om...

On Mon, 19 Mar 2007 16:21:05 -0500, "paii, Ron" <pa**@packairin c.com>
wrote:

Not necessarily.
The way I understand Last, it depends on the sort order. If (unlikely)
we were sorted by SellDate DESC, this query would retrieve the first
amount. Better be explicit.

Alternatively (and to me more reliably) you'd get the max SellDate per
Job in another query, and then join that with the JobSales table,
joining on both fields, to get some fields such as SellPrice for the
max date per job.

-Tom.

I have a sales table that logs sales information for each active job at

the

end of each month. My question is, will the following query give me the

last

sell price for each job in the sales table?

SELECT DISTINCTROW tblJobSales.Job , Last(tblJobSale s.SellPrice) AS

SellPrice

FROM tblJobSales WHERE (((tblJobSales. SellDate) Between #1/1/2007# And
#12/31/2007#)) GROUP BY tblJobSales.Job ;

I have been using Max(tblJobSales .SellPrice), but I can have jobs where

the

sell price goes down from one month to next.
Jobs that were completed in February would not have a record in March.

[paii, Ron]

Why would the following query not give me the max date for each job?

SELECT DISTINCTROW tblJobSales.Job , Last(tblJobSale s.SellDate) AS
LastOfSellDate
FROM tblJobSales
GROUP BY tblJobSales.Job
ORDER BY tblJobSales.Job , Last(tblJobSale s.SellDate);

This one works.

SELECT DISTINCTROW tblJobSales.Job , Max(tblJobSales .SellDate) AS
MaxOfSellDate
FROM tblJobSales
GROUP BY tblJobSales.Job
ORDER BY tblJobSales.Job ;

"paii, Ron" <pa**@packairin c.comwrote in message
news:hu******** *************** *******@athenet .net...

Thanks for your response Tom.

I was thinking I would need a 2nd query sorted by date, but was hoping the
WHERE clause would have the same affect. This query actually worked on my
current data, but I was concerned that it would fail when more records

were

added.

"Tom van Stiphout" <no************ *@cox.netwrote in message
news:g4******** *************** *********@4ax.c om...

On Mon, 19 Mar 2007 16:21:05 -0500, "paii, Ron" <pa**@packairin c.com>
wrote:

Not necessarily.
The way I understand Last, it depends on the sort order. If (unlikely)
we were sorted by SellDate DESC, this query would retrieve the first
amount. Better be explicit.

Alternatively (and to me more reliably) you'd get the max SellDate per
Job in another query, and then join that with the JobSales table,
joining on both fields, to get some fields such as SellPrice for the
max date per job.

-Tom.

>I have a sales table that logs sales information for each active job at

the

>end of each month. My question is, will the following query give me the

last

>sell price for each job in the sales table?
>
>SELECT DISTINCTROW tblJobSales.Job , Last(tblJobSale s.SellPrice) AS

SellPrice

>FROM tblJobSales WHERE (((tblJobSales. SellDate) Between #1/1/2007# And
>#12/31/2007#)) GROUP BY tblJobSales.Job ;
>
>I have been using Max(tblJobSales .SellPrice), but I can have jobs where

the

>sell price goes down from one month to next.
>Jobs that were completed in February would not have a record in March.
>