Jeff, Brian and Elmer,

	Thanks for your effort actually the UPDATE statement given by Brian did work. I think
it was really on back end database issue.  May be Access do not support those two other
UPDATE statement formats :(

	I'm really using Query Designer of Access but when it comes to INSERT and UPDATE I
don't know how to do it in the Query Designer :D

	With this issue do you think I would be having a hard time and problem may arise
if I try to switch to SQL?

Thanks,

enzo c",)?
http://www.tropangwatakwatak.tk

-----Original Message-----
From: ELMER PARICA [mailto:elmer_parica@y...]
Sent: Friday, December 20, 2002 2:19 AM
To: sql language
Subject: [sql_language] RE: Updating One Table with Data from the Other
Table



Hi En,
I can't see any problem. Syntax seems okay? Is there any you didn't explain?
 Enzo Zaragoza <enzaux@g...> wrote:
Here's the exact code I have:

UPDATE tblLkUpAreaBarangay SET tblLkUpAreaBarangay.TownID 
(SELECT tblLkUpAreaTown.TownID FROM tblLkUpAreaTown
WHERE tblLkUpAreaTown.TownCode = tblLkUpAreaBarangay.TownCode
AND tblLkUpAreaTown.ProvID = tblLkUpAreaBarangay.ProvID)

Both are tables.

Thanks,

enzo c",)?
http://www.tropangwatakwatak.tk

-----Original Message-----
From: Jeff Mason [mailto:je.mason@a...]
Sent: Wednesday, December 18, 2002 7:42 PM
To: sql language
Subject: [sql_language] RE: Updating One Table with Data from the Other
Table


Post EXACTLY the queries you tried to execute.

You are not telling us the whole story, because the form of both suggestions
is correct. Is Table2 a table or a view?

--
Jeff Mason Custom Apps, Inc.
Jeff@c...


-----Original Message-----
From: Enzo Zaragoza [mailto:enzaux@g...]
Sent: Wednesday, December 18, 2002 2:23 AM
To: sql language
Subject: [sql_language] Updating One Table with Data from the Other
Table



I have this table which is badly designed by the previous programmer.
Below
is the table structure. As you can see on the Town Table, TownID repeats
for every
Province ID, the same also with the Barangay Table, BrgyID repeats for every
Town.
What I'm gonna do is I will make TownID continuous (autonumber). Of course
if I change
TownID from Town Table I have to change also the TownID from Barangay Table.

Town Table
-------------
ProvID TownID
01 01
01 02
02 01
03 01


Brgy Table
------------
ProvID TownID Brgy ID
01 01 01
01 01 02
02 01 01
03 01 01

What I did is I renamed TownID to TownIDOld, then I created a new
autonumber field
and named it TownID on the Town Table. Now my problem is that how would I
update Barangay
table with the new TownID?

Fee days ago there was a post regarding this matter and Richard & Brian
gave this UPDATE
statement:
UPDATE
SET . = table1.column2
FROM TABLE1 INNER JOIN ON . = .

This didn't work, syntax error.

A friend of mine suggested this one:
UPDATE
SET . = (SELECT . FROM WHERE
. = .)

But this didn't worked also, error says "OPeration must use an updatable
query"

Please Advise. I'm Using Access2k.

Thanks,


enzo c",)?
http://www.tropangwatakwatak.tk











---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now