Reputation: 78
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:
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
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