Comparing two fields that are sequential

[laina222]

I have a query I'm trying to run where I have two fields for criteria. One is called Sequence, and the other is Person. I need to be able to pull any Sequences that go beyond '1', so anything that has a sequence 1,2 or 1,2,3, etc and trash those records that only have sequence 1. However, I cannot just do Sequence > 1 because that will get rid of any of the Sequences that are the start of a series, like 1,2,3.
Also, for all series of sequences, I want to only pull those series where the name is not the same throughout the series. For example, I would want records like this:
Record Sequence Person
1 1 Bob
2 2 Dave
But not records like this:
Record Sequence Person
1 1 Bob
2 2 Bob
In other words, the Person has to change from sequence to sequence. It would be alright if I got something like this, however:
Record Sequence Person
1 1 Bob
2 2 Bob
3 3 Dave
Or something like this:
Record Sequence Person
1 1 Bob
2 2 Dave
3 3 Dave
The sequence can go as high as possible - there is no limit.

Thanks for any help in advance!
Laina

 

[BNPMike]

You need a key first so you can mark the sequences. In your eaxample you have four groups of records which you clearly regard as sharing something. However SQL can't see what you can. So you need:

1 1 Bob A
2 2 Dave A
1 1 Bob B
2 2 Bob B
1 1 Bob C
2 2 Bob C
3 3 Dave C
1 1 Bob D
2 2 Dave D
3 3 Dave D


Then you can select by asking SQL questions like Select all where groupkey is in (select groupkey where sequence number =2) [since any run of more than 1 must have a 2] or where the count of records in a groupkey is greater than 1.

 

[laina222]

That definitely helped me get rid of all the records where there was more than one Sequence! Thank you! However, that does not give me only the records where the Person changes within the Sequence...any idea on how to do that part???

Thanks again! At least I'm further than before