Create start and end number for 'blocks' of contiguous numbers

[Deb]

I have a large table with one column containing numbers. After sorting the
column, I need to identify the start and end number for each set of
contiguous numbers. Can I do this in Access? If so, how? e.g.

1234 -
1235 - Block: StartNum=1234 EndNum=1236
1236 -
2001
3004
4995 - Block: StartNum=4995 EndNum=4996
4996 -
5320

I'd like to compare the number in one row to the number in the next row and
determine if the numbers are contiguous. For each group of two or more
contiguous numbers, a start and end number for that found 'block' would be
established.

 

[ti976]

Yes,
easiest/fastest way is thru sorted recordset(rs) call using VBA, where
you loop thru rs checking the the next record for contiguousness; if
true save previous value as start and then check for next noncontiguous
record and save as end value. Write or output value as needed.
if you prefer a query type result(probably slow and will req 4
queries):

a. create query that will have 2 field that uses 'dcount' function to
look for the +1 and -1 value of the number,therefore, if dcount of +1 =
1 and -1 = 0 then that is a start number while 0,1(respectively)
indicate end number,ex:
less: DCount("[Number]","tblNum",("[Number] = " & ([Number]-1)))
more: DCount("[Number]","tblNum",("[Number] = " & ([Number]+1)))

b. create 2 query, one that filter out the start and one to filter the
end value. Create a field in each query to count the number of
start/end values < the original number, ex:
assignnumber: DCount("[Number]","query created in a","[startend] =
'start' and [Number] < " & [number]); this will by default number the
query records

c. inner join the 2 queries in b using 'assignnumber' field

 

[ti976]

Sweet!
I like how you wrote it. Elegant way of doing my section a. Can you
extend it to pair the startnumber and endnumber in the same record (and
in one query)?(thus section b & c for me) I'm incline to say yes, but
not incline to try to do it.

Deb:
I looked at your question again and realized neither of us actually
produced what you are looking for. modification to section c or another
query will be needed to produce the results you are looking for.

 

[Deb]

Thanks so much for your feedback. Haven't had a chance to look at the dialog
and suggestions provided.

Jamie - the 'general solution' you provided...I'd like to figure out the
sequel as I think it'll provide me with what I need. However, while I use
Access all the time, I'm not used to creating queries this involved and have
not used visual basic. So, I kind of need help with the basics.

1. It looks like three tables are used; Test, T1, T2. But, am confused as to
what these represent. I'm starting out with one table only - with one column
of numbers (Test).
2. Is this meant to be entered as SQL in one query?

I'm unsure of how to get started. So, if there's some feedback you can
provide that would help me utilize the SQL provided, I'd really appreciate it.

 

[John Vinson]

1. It looks like three tables are used; Test, T1, T2. But, am confused as to
what these represent. I'm starting out with one table only - with one column
of numbers (Test).

These are three *aliases* for the same table. He's including the Test
table three times in the query, calling one instance Test, the second
T1, the third T2. This is called a "Self Join" query, since the table
is being joined to itself; to do this, you need to assign a different
name to each instance of the table so you can refer to the fields
unambiguously.

2. Is this meant to be entered as SQL in one query?

Yes.

John W. Vinson[MVP]

 

[Deb]

Got it! Thanks so much!

These are three *aliases* for the same table. He's including the Test
table three times in the query, calling one instance Test, the second
T1, the third T2. This is called a "Self Join" query, since the table
is being joined to itself; to do this, you need to assign a different
name to each instance of the table so you can refer to the fields
unambiguously.


Yes.

John W. Vinson[MVP]