Microsoft Access - 根据字段值更改创建数字序列？
对于这样的查询数据：
+-------+---------+
| Name | Details |
| JEFF | TEST1 |
| JEFF | TEST2 |
| JEFF | TEST3 |
| BOB | TEST1 |
| BOB | TEST2 |
+-------+---------+
如何查询以便添加数字序列（1,2,3...），每次名称更改（即从 JEFF 到 BOB）时重置回 1？
可以使用 DCOUNT 函数吗？
到目前为止我所拥有的是（它没有正确排序）：
Number: (SELECT COUNT(*) FROM [dQuery]
WHERE [dQuery].[Name] = [dQuery].[Name]
AND [dQuery].[sequence] >= [dQuery].[sequence])
UPDATE1：
正确的查询是：
SELECT [dQuery].Name, [dQuery].[sequence], (select count([dQuery].Name) + 1
from [dQuery] as dupe where
dupe.[sequence]< [dQuery].[sequence] and dupe.name = [dQuery].name
) AS [Corrected Sequence]
FROM [dQuery]
WHERE ((([dQuery].Name)="jeff"))
ORDER BY [dQuery].Name, [dQuery].[sequence];
For query data like this:
+-------+---------+
| Name | Details |
| JEFF | TEST1 |
| JEFF | TEST2 |
| JEFF | TEST3 |
| BOB | TEST1 |
| BOB | TEST2 |
+-------+---------+
How do I query so that a numerical sequence (1,2,3...) can be added that resets back to 1 each time the name changes (ie from JEFF to BOB)?
Is it possible to use the DCOUNT function?
What I have so far is (it doesn't sequence correctly):
Number: (SELECT COUNT(*) FROM [dQuery]
WHERE [dQuery].[Name] = [dQuery].[Name]
AND [dQuery].[sequence] >= [dQuery].[sequence])
UPDATE1:
The correct query is:
SELECT [dQuery].Name, [dQuery].[sequence], (select count([dQuery].Name) + 1
from [dQuery] as dupe where
dupe.[sequence]< [dQuery].[sequence] and dupe.name = [dQuery].name
) AS [Corrected Sequence]
FROM [dQuery]
WHERE ((([dQuery].Name)="jeff"))
ORDER BY [dQuery].Name, [dQuery].[sequence];
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
请查看此处。我认为作者已经解决了一些非常相似的问题。
Take a look here. I think the author has solved some very similar issues.
如果您想在报告中动态添加序列号，请为特定表创建报告并在设计视图中打开该报告。然后，在数据行的左侧添加一个文本框，并将“=1”（不带冒号）添加到其“数据”选项卡的“控制源”属性中。并将其“运行总和”属性@“数据”选项卡的“否”更改为“在组上”。在运行时，该文本字段将按顺序显示数据，如每行 1、2、3。
谢谢
If you like to add a serial number in your report dynamically than, create a report for the specific table and open the report in design view. Then, add a text box in the left side of the the data row and give "=1" (with out colon) to its Control Source property @ "Data" tab. And change "No" to "Over Group" of its "running sum" property @ "Data" tab. At the run time that text field will show data in sequence like 1, 2, 3 in every row.
Thanks