compare field in subsequent rows of one table 2

[dingleberry]

Can I compare this using a query

c1 c2
10 data 1
10 data 2
11 data 3
11 data 4
11 data 5
11 data 6
12 data 7
12 data 8
12 data 9
13 data 10

and have it return just where C1 changes so the return would be

10 data 2
11 data 3
11 data 6
12 data 7
12 data 9
13 data10

 

[dhookom]

There would need to be some field that identifies the order of the records. You haven't identified this from your sample.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dingleberry]

You mean how they're sorted?

If so, can I build that into the query so have the query sort the data first and then perform the query? I cannot control how the data is sorted.

 

[PHV]

The real question is:
How do YOU know that '11 data 5' is BEFORE '11 data 6' and AFTER '11 data 4' ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dingleberry]

pretend data 1, data 2, data 3 etc are date stamps. Can I sort on date stamps first oldest to newest in this query and then select from that sorted data the row before and the row of the change of c1?

 

[VRoscioli]

So basically you are looking for the maximum and minimum date for each c1? How about:

SELECT C1, min(C2), max(C2)
  FROM tblTable
  GROUP BY C1
  ORDER BY C1 ASC;

Or if you are really attached to your format...

SELECT C1, C2_Val AS C2
  FROM (
    SELECT C1, min(C2) AS C2_Val
      FROM tblTable
      GROUP BY C1
    UNION ALL
    SELECT C1, max(C2)
      FROM tblTable
      GROUP BY C1
  )
  ORDER BY C1, C2;

-V

 

[PHV]

What about this ?
SELECT c1, Min(c2) AS DateStamp FROM yourTable GROUP BY c1
UNION SELECT c1, Max(c2) FROM yourTable GROUP BY c1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886