How to generate a time phased table in Access-CodePudding

I have an Access query that contains order quantities and reorder frequencies, e.g:

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

I want to generate a time phased table of orders, like this:

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

I'm familiar with MySQL but it seems that I will need to create a VBA subroutine to do this in Access. I'd very much appreciate if someone could point me in the right direction.

CodePudding user response：

You are already in the 'right' direction - need VBA and a 'temp' table. Build temp table with enough WkX fields to accommodate the highest possible interval (max 254). Most likely code will involve opening recordset object, looping records to read values and save records with appropriate data. Maybe this will get you started:

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