how to add new column in ms access table with this criteria

Question

i have two msaccess tables

tableA

textA numA
bd    1
as    0
aa    2
cc    4

tableB

textB 
ss
aa
bd
as

how to write an sql query to add one column called numB in table B so that final table B would be

tableB ( basically fetching values from tableA )

textB  numB
ss     0      ( because ss is not there in table A )
aa     2
bd     1
as     0

Answer 1

You cannot do this in one query. You can create TableC from tables A & B and then rename and/or delete so that table C becomes table B, or you can run a DDL query to add a column to Table B. I suggest the second option.

To add a column:

ALTER TABLE TableB ADD COLUMN NumB Integer

To update:

UPDATE TableA 
RIGHT JOIN TableB ON TableA.TextA = Tableb.TextB 
SET TableB.BumB = Nz([NumA],0);

Answer 2

This SQL will make a new table called tblFooC

SELECT tblFooB.TextB, nz([NumA],0) AS Expr1 INTO tblFooC
FROM tblFooB LEFT JOIN tblFooA ON tblFooB.TextB = tblFooA.TextA;

You could then delete table tblFooB and rename tblFooC to tblFooB