Update Query - Need Help

Hello,

I have two tables with the same fields.  However, in the first one, the older one, some fields have data that the new table does not.  I want to get the data in those fields over into the newer table where the lob is equal.  For instance, in the older table, there is a field called CIO that lists the CIOs for the LOB they represent.  In the newer table however, I have the LOB but not the associated CIO.  How can I link on LOB and get the CIO from the older table into the newer one?

Thanks,
John
jz1englishAsked:
Who is Participating?
 
rockiroadsCommented:
if u have spaces in your tablenames or fieldnames, u have to put them in sqaure brackets
e.g.


update open_remediation_report_source_data, open_remediation_report_source_data_update
set open_remediation_report_source_data_update.cio = open_remediation_report_source_data.cio
where open_remediation_report_source_data.[Eval Name] = open_remediation_report_source_data_update.[Eval Name]


did u backup your db first?
0
 
rockiroadsCommented:
something like this perhaps


update newtable, oldtable
set newtable.cio = oldtable.cio
where newtable.lob = oldtable.log


usual thing is to backup db first
0
 
jz1englishAuthor Commented:
thanks but I don't know sql so i need to know how to enter this int he query editory
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rockiroadsCommented:
what is the exact tablenames and fieldnames? u need to replace what I have done with yours

In MS Access, if u go into Query Editor

add a new query, just write the above but substituting your tablenames/fieldnames
0
 
rockiroadsCommented:
here is some tutorials
http://www.fgcu.edu/support/office2000/access/

What u need to do with u go into a query, is to change from design view to sql view
then do it that way

0
 
jz1englishAuthor Commented:
this is what i put in the sql editor but i got an error:

update open_remediation_report_source_data, open_remediation_report_source_data_update
set open_remediation_report_source_data_update.cio = open_remediation_report_source_data.cio
where open_remediation_report_source_data.Eval Name = open_remediation_report_source_data_update.Eval Name

the error says:

syntax error in query expression: where open_remediation_report_source_data.Eval Name = open_remediation_report_source_data_update.Eval Name
0
 
jz1englishAuthor Commented:
hey rockiroads,

i had to leave the office early yesterday.  i will get in in about an hour and try this out.  let you know how it goes.  thanks....no, i did not back up the db.  no biggie though, if it fails, i can just reimport those two table without a problem.  thanks.......
0
 
rockiroadsCommented:
ok, no worries
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.