Update table with field from second table

[TimGoff]

Hi,

I have 2 tables:

tbl_Bcst_T_Ver
tbl_Bcst_T_Prg

A field exists in both called House_Number. The field in _prg is populated uniquely, _Ver is empty. I need to update the contents from _prg into _ver - understanding some records may duplidate (a one to many relationship exists)

I thought the query would do the trick:

UPDATE tbl_Bcst_T_prg

INNER JOIN tbl_Bcst_T_ver ON tbl_Bcst_T_prg.Programme_Table_ID = tbl_Bcst_T_ver.Programme_ID

SET tbl_Bcst_T_ver.House_Number =
[tbl_bcst_t_prg].[house_number];

Anybody any ideas?

Thanks
Tim

 

[ByteMyzer]

Try this query syntax:

UPDATE tbl_Bcst_T_Ver
SET tbl_Bcst_T_Ver.House_Number = DLookUp("[House_Number]","tbl_Bcst_T_Prg","Programme_Table_ID='" & [Programme_ID] & "'");

 

[TimGoff]

Hi -
thanks for your reply

I tried the query as said. If you run the query in datasheet view I receive the rows back blank - if you try to action the query I get a data type error bizarrely (house_number field are both Text, lengh 50)

I also modified your query slightly, making the fields explicit

UPDATE tbl_Bcst_T_Ver SET tbl_Bcst_T_Ver.House_Number = DLookUp("[tbl_bcst_t_prg.House_Number]","tbl_Bcst_T_Prg","tbl_bcst_t_prg.Programme_Table_ID='" & [tbl_bcst_t_ver.Programme_ID] & "'");