By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,935 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Last vs Max

P: n/a
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(tblJobSales.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.
Mar 19 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Mon, 19 Mar 2007 16:21:05 -0500, "paii, Ron" <pa**@packairinc.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(tblJobSales.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.
Mar 20 '07 #2

P: n/a
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.com...
On Mon, 19 Mar 2007 16:21:05 -0500, "paii, Ron" <pa**@packairinc.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(tblJobSales.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.

Mar 20 '07 #3

P: n/a
Why would the following query not give me the max date for each job?

SELECT DISTINCTROW tblJobSales.Job, Last(tblJobSales.SellDate) AS
LastOfSellDate
FROM tblJobSales
GROUP BY tblJobSales.Job
ORDER BY tblJobSales.Job, Last(tblJobSales.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**@packairinc.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.com...
On Mon, 19 Mar 2007 16:21:05 -0500, "paii, Ron" <pa**@packairinc.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(tblJobSales.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.
>


Mar 20 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.