find x sequential values

[Guest]

I am trying to identify whenever x (in this case 5) sequential values in a
field occur in a table and write these values to another table in the same
database. Can this be done in a query or is vba coding required? Any help
would be appreciated...

 

[Guest]

A little more please. What is the sequence? Is it numbers and how much of a
gap ends a sequence? Or is it alpha-numeric like Brow, Brown, Browne,
Browner, Brownest, etc?

 

[Guest]

The field is check_number, so it is a numeric value. A gap of 1 will end a
sequence. So 1,2,3,4,6,etc... is no good, but I need to capture
1,2,3,4,5,...16,17,18,19,20,...,99,100,101,102,103,104,105 as 3 valid
sequences

 

[John Vinson]

The field is check_number, so it is a numeric value. A gap of 1 will end a
sequence. So 1,2,3,4,6,etc... is no good, but I need to capture
1,2,3,4,5,...16,17,18,19,20,...,99,100,101,102,103,104,105 as 3 valid
sequences

Create a Query by adding the table to the query grid *five times*.
Access will alias the additional instances by adding _1, _2, _3, and
_4 to the tablename. Don't put in any Join lines at all.

Instead, put a criterion

=

.[check_number] + 1

on the table_1 instance of check_number;

=[table_1].[check_number] + 1

on the table_2 instance, and so on.

If you have overlapping runs - like your 99 - 105 - you'll get all the
embedded runs: 99-103, 100-104, 101-105. If you need to get all runs
of five OR MORE with no upper limit, you'll need VBA code, I suspect.

John W. Vinson[MVP]

 

[Michel Walsh]

Hi,

A solution is to rank them, then to subtract this rank from their value:

value rank value-rank
1 1 0
2 2 0
3 3 0
4 4 0
6 5 1
17 6 11
18 7 11
19 8 11
20 9 11
21 10 11
22 11 11
24 12 12
101 13 88
102 14 88
....


Once you have that 'table' (which will be a subquery, in fact), it is only a
matter to GROUP BY on the famous 'value-rank' field, and COUNT their
number of occurrence:

value-rank_group COUNT(*)
0 4
1 1
11 6
12 1
88 2



So, if q1 is the first query, then:


SELECT value_rank
FROM q1
GROUP BY value_rank
HAVING COUNT(*) >=5

would be our query q2 and the final query:


SELECT q1.value
FROM q1 INNER JOIN q2 WHERE q1.value_rank=q2.value_rank


So, the only problem is to get q1. Well, many alternatives, and with MS SQL
Server 2005, you even have a new construction available. With Jet, since I
like joins, someone can use:


SELECT a.value AS value, COUNT(*) AS rank, a.value-COUNT(*) as value_rank
FROM mytable As a INNER JOIN myTable As b
ON a.value >= b.value
GROUP BY a.value




That should be it.


Hoping it may help,
Vanderghast, Access MVP

 

[David F Cox]

If it is 5 and only 5 sequential records then something like:

SELECT Table1.ID, (select count(id) from table1 as t where t.id between
table1.id and table1.id+4) AS x
FROM Table1
GROUP BY Table1.ID
HAVING ((((select count(id) from table1 as t where t.id between table1.id
and table1.id+4))>4));

will identify the sequences, and an it should be easy to generate an append
query from the results..

 

[David F Cox]

if there are only 5 max in a sequence something like this will identify the
sequences and it should be easy to generate an append query from the
results.

SELECT Table1.ID, (select count(id) from table1 as t where t.id between
table1.id and table1.id+4) AS x
FROM Table1
GROUP BY Table1.ID
HAVING ((((select count(id) from table1 as t where t.id between table1.id
and table1.id+4))>4));

 

[Guest]

Running the query you suggested, successive input boxes pop up, "Enter
Parameter Value" for table.check_number, table1.check_number,
table2.check_number, & table3.check_number. Maybe I'm doing something
wrong...

The field is check_number, so it is a numeric value. A gap of 1 will end a
sequence. So 1,2,3,4,6,etc... is no good, but I need to capture
1,2,3,4,5,...16,17,18,19,20,...,99,100,101,102,103,104,105 as 3 valid
sequences

Create a Query by adding the table to the query grid *five times*.
Access will alias the additional instances by adding _1, _2, _3, and
_4 to the tablename. Don't put in any Join lines at all.

Instead, put a criterion

=

.[check_number] + 1

on the table_1 instance of check_number;

=[table_1].[check_number] + 1

on the table_2 instance, and so on.

If you have overlapping runs - like your 99 - 105 - you'll get all the
embedded runs: 99-103, 100-104, 101-105. If you need to get all runs
of five OR MORE with no upper limit, you'll need VBA code, I suspect.

John W. Vinson[MVP]

 

[Guest]

Thanks... this seems to have worked... while it would have been nice to have
one elegantly crafted query to return the required results, the logic of your
solution is quite easy to follow... I was able to identify 58 sequences in a
table of 12,669 records...

 

[Guest]

maybe I have the syntax wrong someplace, but running the query against a db
of 12,000 records sent my PC into an endless look & it never finishes.

 

[John Vinson]

Running the query you suggested, successive input boxes pop up, "Enter
Parameter Value" for table.check_number, table1.check_number,
table2.check_number, & table3.check_number. Maybe I'm doing something
wrong...

Probably just blindly copying my suggested SQL rather than adapting it
to your own circumstances.

I don't know the name of your table, since you didn't happen to post
that information. I used the word "table" as a sample, a model,
anticipating that you would replace it with the name of YOUR table.

If your table is named tblThisCoolStuff, change all the references to
"table" in the query to "tblThisCoolStuff". If your table has some
other name, use it instead.

John W. Vinson[MVP]

 

[Guest]

Thanks... I got it to work, but in the data that I was given there was a
sequence of 14 consecutive check_Number(s)... and unfortunately, I will have
no way of knowing the upper limit of the sequence.

Running the query you suggested, successive input boxes pop up, "Enter
Parameter Value" for table.check_number, table1.check_number,
table2.check_number, & table3.check_number. Maybe I'm doing something
wrong...

The field is check_number, so it is a numeric value. A gap of 1 will end a
sequence. So 1,2,3,4,6,etc... is no good, but I need to capture
1,2,3,4,5,...16,17,18,19,20,...,99,100,101,102,103,104,105 as 3 valid
sequences

Create a Query by adding the table to the query grid *five times*.
Access will alias the additional instances by adding _1, _2, _3, and
_4 to the tablename. Don't put in any Join lines at all.

Instead, put a criterion

=

.[check_number] + 1

on the table_1 instance of check_number;

=[table_1].[check_number] + 1

on the table_2 instance, and so on.

If you have overlapping runs - like your 99 - 105 - you'll get all the
embedded runs: 99-103, 100-104, 101-105. If you need to get all runs
of five OR MORE with no upper limit, you'll need VBA code, I suspect.

John W. Vinson[MVP]

 

[David F Cox]

The people that answer questions on these newsgroups put their reputations
on the line every time they do so, and I admire them for it. Nobody is
immune from error, and a few of us, at least I am, are prone to gianormous
ones from time to time. I saw your response with heavy heart. I knew I had
tested the code posted, but only on about 15 records. So I changed just the
field names and the table names using replace all with notepad from the text
in the posting.

I pressed the go button, and 1 second !!!! later it told me that I had 27595
sequences. This was on an indexed long integer key field.

Some days I feel soooo good.

Good luck with your mission.

This was on a second hand Pentium 3 computer that cost about $60. Access is
an amazing product.

 

[Guest]

I'm sorry that you feel that my response was meant in any way as a
criticism... nothing could be farther from the truth! I do appreciate your
response & I acknowledge any help I receive from any & every source... I
admire & turn to knowledgeable people when I don't have the answer... which
is quite often...

And I did begin my reply with "maybe I have the syntax wrong someplace"...
after all, I do have a tendency for "fat-fingering" the keyboard all too
often. Sorry for any misunderstanding...

 

[David F Cox]

No offence taken whatsoever. I have dyslexic fingers myself, and cannot
match brackets, braces and quotation marks first time of trying to save my
life. I was quite prepared to believe that I had got it wrong (again). I
call it as I see it, and expect the same treatment. If I am wrong I want to
be told, and to learn from it.

I am still thinking about your problem, and still trying to come up with a
really elegant solution, but so far all query based solutions I have found
are tortuous. It needs a "Eureka" moment.

 

[Guest]

This solution is beautiful.

Hi,

A solution is to rank them, then to subtract this rank from their value:

value rank value-rank
1 1 0
2 2 0
3 3 0
4 4 0
6 5 1
17 6 11
18 7 11
19 8 11
20 9 11
21 10 11
22 11 11
24 12 12
101 13 88
102 14 88
....


Once you have that 'table' (which will be a subquery, in fact), it is only a
matter to GROUP BY on the famous 'value-rank' field, and COUNT their
number of occurrence:

value-rank_group COUNT(*)
0 4
1 1
11 6
12 1
88 2



So, if q1 is the first query, then:


SELECT value_rank
FROM q1
GROUP BY value_rank
HAVING COUNT(*) >=5

would be our query q2 and the final query:


SELECT q1.value
FROM q1 INNER JOIN q2 WHERE q1.value_rank=q2.value_rank


So, the only problem is to get q1. Well, many alternatives, and with MS SQL
Server 2005, you even have a new construction available. With Jet, since I
like joins, someone can use:


SELECT a.value AS value, COUNT(*) AS rank, a.value-COUNT(*) as value_rank
FROM mytable As a INNER JOIN myTable As b
ON a.value >= b.value
GROUP BY a.value




That should be it.


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Maybe nice also to note that the question was about finding un-interrupted
continuous sequence of 5, or more, integers among a list of integers (no
duplicate).

It may surprise that a subtraction can occur between different 'things'
(after all, we have been told many time that we can only add, subtract,
apples together). Well, the fact is that the list of integers is, in itself,
a rank (about some unknown value), and therefore, we subtract, effectively,
two ranks (each from a different ranking processes).


Vanderghast, Access MVP