CODEWITHSUNDEEP
sqlms-access
Combinatix
Combinatix

Reputation: 1216

Microsoft Access query returning sequential numbers

Is there any way to create query in MS Access which returns sequential numbers of my choice? For example

10.1
10.2
10.3
10.4
10.5

or 

100
90
80
70
60
50
40
30
20

or

2015-04-10 12:00
2015-04-10 13:00
2015-04-10 14:00
2015-04-10 15:00
2015-04-10 16:00
2015-04-10 17:00
2015-04-10 18:00

I can create temporary table with these values using VBA. I can also create my own VBA function which I put into a query. The question is: Is this possible with pure MS Access query? And if not, what would be the simplest and fastest approach?

EDIT: I don't have to avoid VBA, I just want to find short, clean, and efficient code.

Upvotes: 2

Views: 2461

Answers (2)

Gustav
Gustav

Reputation: 56026

You can use a simple Cartesian Query having a table with numbers from 0 to 9:

SELECT 
    t1.Number + 
    t2.Number * 10 + 
    t3.Number * 100 + 
    t4.Number * 1000 As Factor
FROM 
    tblNumber AS t1, 
    tblNumber AS t2,
    tblNumber AS t3, 
    tblNumber AS t4

or - if you only need small series - just a table with numbers from 0 to 99.

Then for your samples:

SELECT 
    10 + 0.1 * [Number] AS [Value]
FROM 
    tblNumber
WHERE 
    [Number] Between 1 And 5

and:

SELECT 
    [Number] * 10 AS [Value]
FROM 
    tblNumber
WHERE 
    [Number] Between 2 And 10
ORDER BY 
    [Number] * 10 Desc

and:

SELECT 
    DateAdd("h", [Number], #2015-04-10 12:00 PM#) AS [Date]
FROM 
    tblNumber
WHERE 
    [Number] Between 0 And 6

Addendum

A number series can also be built without a dedicated number table by using a system table as source:

SELECT DISTINCT 
    [Tens]+[Ones] AS Factor, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca;

Upvotes: 4

shA.t
shA.t

Reputation: 16978

I think you can use a solution like this:

  1. Make a table (name it t) like this:

    ID | i
---+---
1  | 0
2  | 1
3  | 2
4  | 3
5  | 4
6  | 5
7  | 6
8  | 7
9  | 8
10 | 9

  2. Now use this table for all you need, for example make a query from 0 to 9999 use this query:

    SELECT 
    i1 + i2 * 10 + i3 * 100 + i4 * 1000 AS rowNo
FROM 
    (SELECT 
        t1.i AS i1, t2.i AS i2 
     FROM 
        t AS t1, t AS t2) AS tt1, 
    (SELECT 
        t1.i AS i3, t2.i AS i4 
     FROM 
        t AS t1, t AS t2) AS tt2;

  3. For example you can change rowNo formula to these:

    CDbl('10.'+CStr([i1]+[i2]*10+[i3]*100+[i4]*1000))   =>  10.1, 10.2, ...

    or

    (10000 - ([i1]+[i2]*10+[i3]*100+[i4]*1000)) * 10    => 100000, 99990, ... , 20, 10

    or

    DateAdd("h",[i1]+[i2]*10+[i3]*100+[i4]*1000,#4/10/2015 12:00:00 PM#)

Upvotes: 2

Related Questions