如何在Access中生成时间分阶段表

Question

我有一个包含订单数量和重新订购频率的Access查询，例如:

+-------+---------------------------+---------------------------+
|Product|  Order Qty (pallets)      |    Order Interval (wks)   |
+-------+---------------------------+---------------------------+
| 1234  | 2.5                       | 7                         |
+-------+---------------------------+---------------------------+
| 1235  | 3.4                       | 10                        |
+-------+---------------------------+---------------------------+

我想生成一个订单的时序表，如下所示:

+-------+--------+--------+--------+--------+--------+--------+--------+
|Product|  Wk1   |  Wk2   |  Wk3   |  Wk4   |  Wk5   |  Wk6   |  Wk7   |
+-------+--------+--------+--------+--------+--------+--------+--------+
|  1234 |  2.5   |        |        |        |        |        |  2.5   |
+-------+--------+--------+--------+--------+--------+--------+--------+

我熟悉MySQL，但似乎我需要在Access中创建一个VBA子程序来做到这一点。如果有人能给我指个方向，我将非常感激。

Answer 1

您已经在'正确'的方向-需要VBA和'临时'表。构建具有足够WkX字段的临时表，以容纳尽可能高的间隔(最大254)。最可能的代码将涉及打开recordset对象，循环记录以读取值并保存具有适当数据的记录。也许这会让你开始:

Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM table")
CurrentDb.Execute "DELETE FROM PhaseTable"
Do While Not rs.EOF
db.Execute "INSERT INTO PhaseTable(Product, Wk1, Wk" & rs!Interval) " & _
"VALUES('" & rs!Product & "," & rs!Qty & "," & rs!Qty & ")"
rs.MoveNext
Loop