update from one table to another 1

[cynaptic]

I am have just started a large (for me!) project which involves rationalising 9 various spreadsheets and databases in to one access database. all spreadsheets will be Excel 97 and all Access will be Access97.

I have tried this kind of thing befre and have come unstuck when trying to update records from one table to another. i.e. i have two list of company details one more recent than the other. what i would like to do is update the older version (contains most records) with details from the newer version.

I have tried

update TableA SET TableA.Xvalue = Tableb.Xvalue
From TableB
Where TableA.YValue = Tableb.YValue

I end up with a prompt for a parameter, I am obviously fundemenally not understanding what teh procedure is

help.......

 

[grega]

What about

update TableA
SET TableA.Xvalue = Tableb.Xvalue
From TableA, TableB
Where TableA.YValue = Tableb.YValue

Greg.

 

[tlbroadbent]

The proper Access syntax will be as follows. You should be able to build the query in the Access query design grid.

Update TableA INNER JOIN TableB
ON TableA.YValue = Tableb.YValue
SET TableA.Xvalue = Tableb.Xvalue;

I recommend posting Access questions in the Microsoft: Access Queries and Access/SQL forum - forum701. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cynaptic]

Greg/Terry

thanks for your help, has def helped me see the way forward,
and Terry thanks for the advice. Will join the forum, expect I will need it....

Alex

 

[Guest_imported]

I have the same problem, but the only tool I have is Borland SQL-Explorer (using DBF files)... It doesn't result... Why? and what could I do now?

UPDATE FADEXXX INNER JOIN FCATXXX
ON FADEXXX.DBMEDIDOR=FCATXXX.DBMEDIDOR
SET FADEXXX.DBCONTRA = FCATXXX.DBCONTRA;

help please

 

[tlbroadbent]

JROBLES_2K,

Access SQL (or Jet SQL) is different from ANSI SQL. Most other versions of SQL probably use the following syntax.

UPDATE FADEXXX
SET DBCONTRA = FCATXXX.DBCONTRA
FROM FADEXXX INNER JOIN FCATXXX
ON FADEXXX.DBMEDIDOR=FCATXXX.DBMEDIDOR Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.