Vlookup in Access with SQL

[jogunjobi]

I'm trying to do a vlookup in access using sql and I need some help for an issue that's been giving me headaches and I'll try to explain as best as I can without confusing you.

Table A contains the fields that I'm trying to use for the vlookups. The fields are as follows


Table B is the table that I want to update based on the fields from Table A

My current to update the DEPTID in table B is

UPDATE 
Table B 
INNER JOIN 
Table A 
ON [Table B].COST_CENTER = [Table A].Src_Cost_Ctr
and [Table B].Combo_Map = [Table A].Cmb_ID
and [Table B].LID = [Table A].Src_Com
and [Table B].ACCOUNT = [Table A].Src_Acc
SET 
[Table B].DEPTID = [Table A]![DEPTID];

That works fine for most of the sql queries. However, there will be some times when the "Src_Acc" does not exist in that column. In that instance, I want it the query to return the last DEPTID with the blank "Src_Acc" when it cannot find the account in that column.

Thanks for your help.

[June7]

Is this duplication of data - why?

Will probably need a subquery because need to pull value from another record. Not easy to do. Review this http://allenbrowne.com/subquery-01.html#AnotherRecord