英文:
Ms Access running sum with dates and order inside dates
问题
我正在使用MS Access创建数据库。我有不同的物理容器，我想获得已添加和取出容器内液体的累计和，以得到容器内液体的余额。
最大的问题是液体交易的排序。我已经阅读了很多资源，发现一种做法是将时间添加到日期中以便排序，但是我不应该使用时间，所以我决定在日期中添加手动排序号码，虽然我不确定这是否是实现这一目标的最佳方法，但至少我可以进行排序。
我拥有的字段是：
- 容器编号（ContainerId）
- 交易日期（DateTransaction）
- 排序日期（OrderInDate）
- 数量（Quantity）
数量字段用"-"表示取款，用"+"表示添加，所以如果我能正确获得累计总和，我就能得到余额。
我考虑将排序日期（OrderInDate）添加为秒数到日期中，以正确排序数据，并且我编写了如下的查询（qryInventoryTransactionsOrder）：
SELECT ContainerId, Quantity, DateTransaction, OrderInDate,
DateAdd("s",OrderInDate,DateTransaction) AS Expr1,
DSum("Quantity","qryInventoryTransactionsOrder",
"[ContainerId]=" & [ContainerId] & " AND
[Expr1] <= #" & [Expr1] & "#") AS Balance
FROM InventoryTransactions
ORDER BY ContainerId, DateAdd("s",OrderInDate,DateTransaction);
这会返回类似以下的有趣结果：
| ContainerId | DateTransaction | OrderInDate | Quantity | Expr1 | Balance |
|---|---|---|---|---|---|
| 1 | 29/05/2023 | 1 | -50 | 29/05/2023 00:00:01 | -50 |
| 1 | 31/05/2023 | 1 | 100 | 31/05/2023 00:00:01 | 50 |
| 1 | 31/05/2023 | 2 | 255 | 31/05/2023 00:00:02 | 305 |
| 1 | 01/06/2023 | 1 | -155 | 01/06/2023 00:00:01 | |
| 1 | 01/06/2023 | 2 | -155 | 01/06/2023 00:00:02 | |
| 1 | 01/06/2023 | 3 | 2500 | 01/06/2023 00:00:03 | |
| 1 | 08/06/2023 | 1 | -500 | 08/06/2023 00:00:01 | 1995 |
如您所见，“Balance” 在前三行中是正确的，然后返回3个空结果，然后是1995。
我在这里做错了什么，或者有没有更好的方法来实现这个结果？
英文:
I am using MS Access to create a DB. I have different physical containers and I want to obtain the running sum of the liquid that has been added and taken out of the container to give the balance of the liquid inside the container.
The biggest problem is ordering liquid transactions. I have read lots of resources, and I found out one way of doing this is adding time to the dates so they can be ordered, but I am supposed to not use time, so I have decided to add a manual ordering number within a date which I am not sure is the best way of achieving this, but at least I can do the ordering.
The fields I have are:
-ContainerId
-DateTransaction
-OrderInDate
-Quantity
Quantity is "-" for withdrawal and "+" for additions, so if I can properly get a running total, I will get the balance.
I thought of adding OrderInDate as seconds to the Date to correctly order the data and I have written a query like this (qryInventoryTransactionsOrder):
SELECT ContainerId, Quantity, DateTransaction, OrderInDate,
DateAdd("s",OrderInDate,DateTransaction) AS Expr1,
DSum("Quantity","qryInventoryTransactionsOrder",
"[ContainerId]=" & [ContainerId] & " AND
[Expr1] <= #" & [Expr1] & "#") AS Balance
FROM InventoryTransactions
ORDER BY ContainerId, DateAdd("s",OrderInDate,DateTransaction);
This returns very interesting result like this :
| ContainerId | DateTransaction | OrderInDate | Quantity | Expr1 | Balance |
|---|---|---|---|---|---|
| 1 | 29/05/2023 | 1 | -50 | 29/05/2023 00:00:01 | -50 |
| 1 | 31/05/2023 | 1 | 100 | 31/05/2023 00:00:01 | 50 |
| 1 | 31/05/2023 | 2 | 255 | 31/05/2023 00:00:02 | 305 |
| 1 | 01/06/2023 | 1 | -155 | 01/06/2023 00:00:01 | |
| 1 | 01/06/2023 | 2 | -155 | 01/06/2023 00:00:02 | |
| 1 | 01/06/2023 | 3 | 2500 | 01/06/2023 00:00:03 | |
| 1 | 08/06/2023 | 1 | -500 | 08/06/2023 00:00:01 | 1995 |
As you will see "Balance" is correct for the first 3 lines, then it returns 3 empty results, and then 1995.
What am I doing wrong here or is there a better way to achieve this result?
答案1
得分: 1
无法在MS Access中对递归查询做出任何陈述。这很有趣。
尝试这个查询，其中DSum从主表中计算总和。
<details>
<summary>英文:</summary>
I can't say anything about recursive queries in MS Access.It is interesting.
Try this query, where DSum counts sum from main table.
SELECT ContainerId, Quantity, DateTransaction, OrderInDate
,DateAdd("s",OrderInDate,DateTransaction) AS Expr1
,DSum("Quantity","InventoryTransactions"
,"[ContainerId]=" & [ContainerId]
& " AND Format(DateAdd(""s"",OrderInDate,DateTransaction),""yyyyMMddhhmmss"") <= "
& format(DateAdd("s",OrderInDate,DateTransaction),"yyyyMMddhhmmss") & "")
AS Balance
FROM InventoryTransactions
ORDER BY ContainerId, DateAdd("s",OrderInDate,DateTransaction);
</details>
# 答案2
**得分**: 0
你不能简单地连接日期值。你必须强制使用一个中性格式来表示此字符串表达式：
```sql
SELECT
ContainerId,
Quantity,
DateTransaction,
OrderInDate,
DateAdd("s",OrderInDate,DateTransaction) AS Expr1,
DSum(
"Quantity",
"qryInventoryTransactionsOrder",
"[ContainerId]=" & [ContainerId] & " AND [Expr1] <= #" & Format([Expr1], 'yyyy/mm/dd hh:nn:ss') & "#") AS Balance
FROM
InventoryTransactions
ORDER BY
ContainerId,
DateAdd("s",OrderInDate,DateTransaction);
英文:
You can'1 concatenate the date value that simple. You must force a neutral format for the string expression of this:
SELECT
ContainerId,
Quantity,
DateTransaction,
OrderInDate,
DateAdd("s",OrderInDate,DateTransaction) AS Expr1,
DSum(
"Quantity",
"qryInventoryTransactionsOrder",
"[ContainerId]=" & [ContainerId] & " AND [Expr1] <= #" & Format([Expr1], 'yyyy\/mm\/dd hh:\nn\:ss') & "#") AS Balance
FROM
InventoryTransactions
ORDER BY
ContainerId,
DateAdd("s",OrderInDate,DateTransaction);
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论