Select rows where value changes

I have a table in an access db with columns for job description, project, date, and amount needed. For each description, I want to select the first date entry for each job/project combo, as well as the rows where the amount changes.

So, if I have

Job        proj date     amt
Programmer 105  01-01-11 3
Programmer 105  01-02-11 3
Programmer 105  01-03-11 2
Programmer 105  01-04-11 2
Programmer 105  01-05-11 3
Programmer 110  01-03-11 1
Programmer 110  01-04-11 2
Programmer 110  01-05-11 3
Manager    105  01-01-11 1
Manager    105  01-02-11 2
Manager    105  01-03-11 2
Manager    105  01-04-11 2
Manager    10开发者_如何学JAVA5  01-05-11 2

I want to select

Programmer 105  01-01-11 3
Programmer 105  01-03-11 2
Programmer 105  01-05-11 3
Programmer 110  01-03-11 1
Programmer 110  01-04-11 2
Programmer 110  01-05-11 3
Manager    105  01-01-11 1
Manager    105  01-02-11 2

I have a table with the distinct job descriptions that can be used.

The first entry will always have Null for nextamt:

SELECT j.Job,
    j.proj,
    j.Date,
    j.amt,
    (SELECT TOP 1 amt 
     FROM jobs q 
     WHERE q.job=j.job AND q.proj=j.proj AND q.date<j.date 
     ORDER BY [date] DESC) AS nextamt 
FROM jobs AS j 
WHERE ((SELECT TOP 1 amt 
        FROM jobs q 
        WHERE q.job=j.job AND q.proj=j.proj AND q.date<j.date 
        ORDER BY [date] DESC)) <>[amt] 
Or ((SELECT TOP 1 amt 
     FROM jobs q 
     WHERE q.job=j.job AND q.proj=j.proj AND q.date<j.date 
     ORDER BY [date] DESC)) Is Null

This can be done easily with analytic (window) functions, available in Oracle/SQL-Server/etc (Google for lag, lead, first_value, last_value etc etc), but Access doesn't support them, so you have to do self join something like this:

SELECT 
    rows.Job, rows.proj, rows.dte, curr.amt 
FROM
    ((SELECT Data.Job, Data.proj, Data.dte, 
        (SELECT max(dte) 
         FROM data as prev 
         WHERE prev.job = data.job and 
               prev.proj = data.proj and 
               prev.dte < data.dte) AS prev_dte 
     FROM Data) as [rows] 
     INNER JOIN Data AS curr 
               ON (rows.dte = curr.dte) AND 
                  (rows.proj = curr.proj) AND 
                  (rows.Job = curr.Job)
)
    LEFT JOIN Data AS prev 
         ON (rows.prev_dte = prev.dte) AND 
            (rows.proj = prev.proj) AND 
            (rows.Job = prev.Job) 
WHERE 
    prev.amt<>[curr].[amt] OR 
    prev.Job Is Null;

So, inline view called [rows] finds max date less than each date in your table, then inner join back to get current row and outer join for prev row so that you will pick up first row.

(Having problems with the code formatting - sorry.)

Edit : here is the output, which I think is what you required.

Job         proj    dte             amt
Programmer  105     01/01/2011  3 
Programmer  105     03/01/2011  2 
Programmer  105     05/01/2011  3 
Programmer  110     03/01/2011  1 
Programmer  110     04/01/2011  2 
Programmer  110     05/01/2011  3 
Manager     105     01/01/2011  1 
Manager     105     02/01/2011  2