Adding a Column to a table

[John_]

I have a Make Table query. Once this table is created, later in the process,
I would like to add a field to this same table. I need to do a calculation
using some of the data in other fields within this same table and place the
results in this new field. Normally, I would append the data in this table
to another table and include this new field and then do the calculation in
this new table.

However, this table that was created in the 'Make Table' query is used in a
lot of other process and I am wondering if there is a command similar to the
RunSQL macro command

Alter Table [table name] Drop Column [field]

that would allow me to add, rather than drop a column from this table.
Thanks for your help!

 

[John W. Vinson]

I have a Make Table query. Once this table is created, later in the process,
I would like to add a field to this same table. I need to do a calculation
using some of the data in other fields within this same table and place the
results in this new field. Normally, I would append the data in this table
to another table and include this new field and then do the calculation in
this new table.

However, this table that was created in the 'Make Table' query is used in a
lot of other process and I am wondering if there is a command similar to the
RunSQL macro command

Alter Table [table name] Drop Column [field]

that would allow me to add, rather than drop a column from this table.
Thanks for your help!

WHOA. You may be way off on the wrong track.

First off, MakeTable queries are *VERY* rarely necessary or appropriate. There
is very little that you can do with a Table which you cannot do with a Select
Query. You may be assuming that you must create a table in order to base a
Report or an Export on it; that assumption is incorrect, however!

At the very least, you can create the table initially, empty, with all the
fields that you will ever need, and run an Append query to fill it.

Secondly, storing a calculated field is also very rarely needed. Storing
derived data such as this in your table accomplishes three things: it wastes
disk space; it wastes time (almost any calculation will be MUCH faster than a
disk fetch); and most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data in your table
WHICH IS WRONG, and no automatic way to detect that fact.

Just redo the calculation whenever you need it, either as a calculated field
in a Query or in the control source of a Form or a Report textbox.

If you have a good reason to do this anyway, just include a NULL value in the
MakeTable query (or include the calculation directly in the MakeTable query).
I very strongly doubt that it's necessary to have either this field or even
this table AT ALL, however!