Microsoft Access查询返回序号
有什么方法可以在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;