Easy way to update 40 columns from table A to table B

[Ynot]

Is there an easy way to update 40 columns from one table to another. The
only examples I have seen are using joins but I don't have a good field to
join on.



The application adds a job to a schedule then a technician fills in all of
the pertinent components and specifications. In many cases this job is
similar to a job which was done in the past so they have saved these
"prototype jobs" in a prototype table. They would like the technician to
simply select the new job, then select a prototype job that is appropriate
and say "update". This should transfer all components and specification
from the prototype columns to the new job columns.



The only remaining task is for the technician to fill in any additional
information required and that is already part of the existing application.



Any guidance is greatly appreciated

 

[Guest]

You shouldn't have to copy all the data if it's the same or each 'prototype'. Storing it two places is redundant and wastes system resources

What you want to do is create a tblPrototypes table, listing a unique PrototypeID ( pt_ID ) for each plus your 40 detail columns plus anything else you want to toss in

Make the pt_ID field the PrimaryKey for the ProrotypeID field.
(If you need to generate unique ID's, make a structure-only copy of your table, add an AutoNumber field to the new copy and then append all the data from your original table. As the records populate, they'll each be given an incremental number.

Next, make a tblJobs table, with a unique job_ID field as the PrimaryKey, a pt_ID field for the prototype the job is using and any other fields you want to add

Create a One-to-Many Relationship between the pt_ID field in both tables and enforce referential integrity

This should let you track the prototype specs for each job, display them whenever you need, and not have to enter or store the data twice

Hope this helps

Howard Brod



----- Ynot wrote: ----

Is there an easy way to update 40 columns from one table to another. Th
only examples I have seen are using joins but I don't have a good field t
join on



The application adds a job to a schedule then a technician fills in all o
the pertinent components and specifications. In many cases this job i
similar to a job which was done in the past so they have saved thes
"prototype jobs" in a prototype table. They would like the technician t
simply select the new job, then select a prototype job that is appropriat
and say "update". This should transfer all components and specificatio
from the prototype columns to the new job columns



The only remaining task is for the technician to fill in any additiona
information required and that is already part of the existing application



Any guidance is greatly appreciate

 

[Ynot]

Howard thanks and I agree. I just don't have an easy way to do this. The
"jobs" table is part of a large old system. That system works with all of
the columns in the jobs table. In order to do it right the old system will
have to be redone, that is planned but off in the future.



I am still stuck having to do an update of about 40 columns from one table
to another any other ideas?

You shouldn't have to copy all the data if it's the same or each

'prototype'. Storing it two places is redundant and wastes system
resources.

What you want to do is create a tblPrototypes table, listing a unique

PrototypeID ( pt_ID ) for each plus your 40 detail columns plus anything
else you want to toss in.

Make the pt_ID field the PrimaryKey for the ProrotypeID field.
(If you need to generate unique ID's, make a structure-only copy of your

table, add an AutoNumber field to the new copy and then append all the data
from your original table. As the records populate, they'll each be given an
incremental number.)

Next, make a tblJobs table, with a unique job_ID field as the PrimaryKey,

a pt_ID field for the prototype the job is using and any other fields you
want to add.

Create a One-to-Many Relationship between the pt_ID field in both tables

and enforce referential integrity.

This should let you track the prototype specs for each job, display them

whenever you need, and not have to enter or store the data twice.