Help with query in Microsoft Access

Question

I have 2 tables:

1. Table A: code | name

2. Table B: barcode | name

Table B has full barcode and name, Table A has only code.

I need to run update query that fill name in Table A.

I tried something like:

update A set name = (select top 1 Name from B where B.Code = mid(A.Barcode,1,8))

but it doesn't work.

Answer 1

Name is a reserved word, you need to put it in square brackets: [name]

In general, fields (columns) should not be named with reserved words.

List of reserved words in Access 2002 and in later versions of Access

Answer 2

Based on your description, it looks like your WHERE clause has table and field names mismatched. Instead of:

where B.Code = mid(A.Barcode,1,8)

My guess is you wanted:

where A.Code = mid(B.Barcode,1,8)

However, perhaps you don't need a separate table A. If A's information can be derived from table B, just use a query in place of table A.

SELECT Mid(B.Barcode,1,8) AS Code, B.[Name] FROM B;