Tags: access, active, database, following, job, logs, max, microsoft, mysql, oracle, query, sales, sql, table, theend

Last vs Max

On Database » Microsoft Access

11,863 words with 3 Comments; publish: Fri, 04 Jan 2008 13:03:00 GMT; (25062.50, « »)

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.

All Comments

Leave a comment...

  • 3 Comments
    • On Mon, 19 Mar 2007 16:21:05 -0500, "paii, Ron" <paii.ms-access.todaysummary.com.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.

      Quote:
      === Original Words ===

      >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.

      >

      #1; Fri, 04 Jan 2008 13:04:00 GMT
    • 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.spam.tom7744.ms-access.todaysummary.com.cox.netwrote in message

      news:g4euv25tj9nqcqkpvvg0frpvr1o9ftvvc6.ms-access.todaysummary.com.4ax.com...

      Quote:
      === Original Words ===

      sell price goes down from one month to next.

      Jobs that were completed in February would not have a record in March.

      >

#2; Fri, 04 Jan 2008 13:05:00 GMT
  • >

    >

    #3; Fri, 04 Jan 2008 13:06:00 GMT