Microsoft Access查询返回序号 [英] Microsoft Access query returning sequential numbers

问题描述

有什么方法可以在MS Access中创建查询，该查询返回我选择的序号?例如

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

或

100
90
80
70
60
50
40
30
20

或

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

我可以使用VBA使用这些值创建临时表.我还可以创建自己的VBA函数，并将其放入查询中.问题是:使用纯MS Access查询可能吗?如果没有，那么最简单，最快的方法是什么?

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?

我不必避免使用VBA，我只想找到简短，干净且有效的代码.

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

推荐答案

您可以使用简单的笛卡尔查询，该查询的表的编号从0到9:

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

或者-如果只需要小序列-只需一张数字从0到99的表.

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

然后为您的样品:

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

和:

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

和:

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

附录

也可以通过使用系统表作为源来构建没有专用编号表的编号系列:

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;

这篇关于Microsoft Access查询返回序号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！