DDL query to add new fields to existing table

[Julian]

Access 2003.

Is it possible to use a SQL statement to copy x number of fields from table
A to the end of table B? Table B does not already contain the fields I wish
to copy. The fields I want to copy only exist in table A and not yet in
table B.

Thus the query needs to select x number of fields from table A and copy the
fields to the end of table B where they do not currently exist.

I tried this SQL statement:

INSERT INTO tmp_data ( CoPayFundGrant, M_DSS_SSA_Benefits,
O_DSS_SSA_Benefits, Ref_MPS, Ref_LA, Ref_DCMH, Place_Comm, Rem_Comm,
Core_Case_Man, Ref_DCMH_Case_Man, Program, HistoryDrugUse )
SELECT tblData.CoPayFundGrant AS Expr1, tblData.M_DSS_SSA_Benefits AS Expr2,
tblData.O_DSS_SSA_Benefits AS Expr3, tblData.Ref_MPS AS Expr4,
tblData.Ref_LA AS Expr5, tblData.Ref_DCMH AS Expr6, tblData.Place_Comm AS
Expr7, tblData.Rem_Comm AS Expr8, tblData.Core_Case_Man AS Expr9,
tblData.Ref_DCMH_Case_Man AS Expr10, tblData.Program AS Expr11,
tblData.HistoryDrugUse AS Expr12
FROM tblData;

But I get the error "The INSERT INTO statement contains the following
unknown field 'CoPayFundGrant'......". That happens because the field does
not exist in table B yet. I want a query which will create the field and
place the original data in it.


Thanks.

 

[david epsom dot com dot au]

You need to run an Alter Table query to add the new fields,
then an Update or Append query to copy the data.

(david)