CODEWITHSUNDEEP
sqlvb.netms-access
SpyingSphinx
SpyingSphinx

Reputation: 78

SQL to Access database which allows me to select 'n' consecutive numbers

This maybe a bit of a big favor to ask, but need a little help trying to get certain set of data from an access database using VB.NET. I have found a great article and the question is what I want basically, however the solutions given are unfortunately not for access.

See the thread here: https://dba.stackexchange.com/questions/36943/find-n-consecutive-free-numbers-from-table

The code that works for me and would be perfect is here: http://sqlfiddle.com/#!1/a2633/2

Ideally I'd have a proper SQL database but unfortunately I'm stuck with MS Access, and I know it doesn't accept ROW_NUMBER or PARTITION BY ... how do I know? I've tried and googled :~)

Basically I have a list of numbers in a database as below:

Access Table My current SQL is 

SELECT TOP 5 
    [ID], [UIDNo] 
FROM 
    [tblUIDS] 
WHERE 
    [Requester] IS NULL 
ORDER BY 
    ID

The 5 is a variable where the user says i want x amount, so the SQL statement returns the top number of UIDNos, which is grand, it then does an INSERT statement to the requester. However id like it to be able to return 5 UIDNo that are sequential.

So based on the above image if the user wants 2 UIDNo it will return 00004 & 00005, instead of just returning 00001 & 00004. If the user wants 6 UIDNo it will return 00032-00037 etc etc.

Does using VB.NET even allow me to query a MS Access database like this? I'm open to suggestions as well if its going to be easier another way...

Thanks in advance

Upvotes: 0

Views: 140

Answers (1)

donPablo
donPablo

Reputation: 1969

No temp table required. Yes, code is pretty complex, but you can follow it.

Many thanks to the references you cited.

Currently set to get 5 records

SELECT TOP 5 [id],
             [uidno]
FROM   consec
WHERE  uidno >= (SELECT First(first_number) AS StartAt
                 FROM   (SELECT Min(uidno) AS first_number,
                                Count(*)   AS ct_free
                         FROM   (SELECT id,
                                        uidno,
                                        Dcount("uidno", "consec",
                                        "uidno <=" & [uidno])
                                        AS
                                        Counter,
                                        uidno - counter
                                        AS Grp
                                 FROM   (SELECT TOP 90000 [id],
                                                          [uidno]
                                         FROM   consec
                                         WHERE  [requester] IS NULL
                                         ORDER  BY uidno,
                                                   id) AS in1) x
                         GROUP  BY grp
                         HAVING Count(*) >= 5
                         ORDER  BY grp))

Delivers

id      uidno
615092  32
615093  33
615094  34
615095  35
615096  36

Upvotes: 2

Related Questions