Copy existing fields from tbl1 to tbl2.

  • Thread starter Thread starter Julian
  • Start date Start date
J

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. They only exist in table A.

Thanks.
 
An append query will do this for you. By the way, there is no "end" of the
table specifically to which records are added -- you just add records to the
table (no specific order is maintained by the table itself -- all "order of
records" is done by queries' ORDER BY clauses.
 
The Append query only works if the fields being copied already exist in the
destination table. Here's an example:

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;

The above when run, gives the error "The INSERT INTO statement contains the
following unknown field 'CoPayFundGrant'......". If I manually create the
field "CoPayFundGrant", when run again, the above says the same for the
"M_DSS_SSA_Benefits" field.
 
Oh ho - you want to make new fields in the table?

This can be done with a DDL (data definition language) query (an "ALTER
TABLE" type of query). I am not an expert at such queries and unfortunately
don't have time tonite to do a bit of research for your question (too bad,
as I like to learn new things!), but they are described in Help files.

Or repost your question with a subject line such as this:

"DDL query to add new fields to existing table"
 
Back
Top