What type of query do I need?

[warriner]

Apologies - I bet this has been asked 100 times before. I'm an Access beginner and have been asked by my boss to create an Access database. I have 2 tables: one of data relating to individual locations and the other which is an order form datasheet. It's a bit like this:

Table A

Code	Item	Quantity	Route	Stop
000ABC	LABELS	1
001DEF	BAGS	3
002GHI	TAPE	1

The route and stop column are blank as I would like to feed this information, using the 'Code' from my second table which looks a bit like this:

Table B

Code	Route	Stop
000ABC	1003	23
001DEF	1027	12
002GHI	1074	28

Essentially, when I create a new record, I want to enter the 'Code' in Table A and the Route & Stop automatically pull from Table B.

I tried using a lookup field which has all of the info in the dropdown, but then when I select it, it only records the code, not the route and stop.

I tried append but this created new records containing the route and stop - I didn't seem to be able to get this to work.

Please can somebody explain exactly how I can do this? You may need to dumb it down a little!

[ranman256]

An UPDATE query.
in a query bring in both tables, join on CODE field.

update tblA.Route to tblB.Route
and same with STOP

[warriner]

An UPDATE query.
in a query bring in both tables, join on CODE field.

update tblA.Route to tblB.Route
and same with STOP

Do I need to put anything special in the ‘update to’ row or just tblA.Route for example? Square brackets or anything?