What i would like to do is have an automatic query so when i type in a new person in the "person" table it would look up the "car no", from the car no table and then input the model into the "car model field".
So in the example above it would put Vauxhall into "Car model" for matt and edward and BMW in the "car model field" for John.
I think i have to use an update query, but im not sure which fields to select and what to put in the "update to" column.
If you need any more info then please let me know.
I'm afraid you're on the wrong way : You are making redundant data. This means that you have twice the same data in different tables which will make your database inconsistent.
Just forget the field Car Model in your Persons table! When you assign a Car No to a person, it is already known what model that will be because this is stated in your Car table.
What you have to do :
-dump the field CarModel in table Persons
-make the field CarNo in table Cars your Primary Key
-create a relation between Persons and Cars on the fields CarNo
If you want to see the car model, you can create a query based on both tables and then use that query to create a form.
SQL code for the query : SELECT Persons.PersonName, Persons.PersonCarNo, Cars.CarModel FROM Persons INNER JOIN Cars ON Persons.PersonCarNo = Cars.CarNo;