How to change the table from a make-ready table query

[merlynsdad]

I have a table made from a make-table-qry. I'm using calculated data from the table in VBA to calculate variable values, which I would like to add to the table as additional columns. For instance

Do While rst1![event_type] = 4
                        datDuration = datDuration + rst1![duration]
                        If rst1![event_type] <> 4 Then Exit Do
                        rst1.MoveNext
                    Loop

All of the rst1 references are to the table data. I would like to put the value of datDuration back into the table as TotalIdle. Is this possible, and if so, how is it done?

If the square peg won't fit in the round hole, sand off the corners.

 

[PHV]

Why not simply use an UPDATE query ?
Furthermore, it's rarely advisable to store calculated/derived values in tables ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[merlynsdad]

Since TableIdle isn't in the table, don't I need to run ALTER TABLE first to add it?
strSQL = "ALTER TABLE tblSEIDtoExt_Num " _
& "ADD TotalIdle date;"
db.Execute strSQL
That doesn't want to add the column. Does that work on a maketable query table?

If the square peg won't fit in the round hole, sand off the corners.

 

[merlynsdad]

I made another make table query and put one column in it. Then I ran it, and it has one column. Then I ran

 strSQL = "ALTER TABLE tblAlterTest " _
                        & "ADD TotalIdle date, Available date;"
                    DoCmd.RunSQL strSQL

The TotalIdle and Available columns showed up in the new table.

However, when I run qrySEIDtoExt_Num to make tblSEIDtoExt_Num, then run the same code as above (changing the table name) it does not add the additional columns.

If the square peg won't fit in the round hole, sand off the corners.