Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    31

    Unanswered: Updating main table

    Hi All,

    This is prob a really easy question but im not sure how to do it.

    I have 2 tables. Person and car.

    In the "person" table i have the fields Name, Car No, and Car model.

    In the "car" table i have the fields car no and car model.

    The car table is a fixed table which looks like this

    Car No---------Car Model

    1---------------Jaguar
    2---------------Ford
    3---------------Vauxhall
    4---------------BMW
    etc

    The person table at the moment looks like this, there are about 50 records at the moment.

    Name----------Car No--------Car Model

    Matt-------------3--------------
    John-------------4-------------
    Edward----------3------------

    The field car model is blank.

    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.

    Thanks for your help.

    Matt

  2. #2
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Hello Matt,

    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;

  3. #3
    Join Date
    Aug 2003
    Posts
    31

    Hi

    Hi Herman,

    I actually need the column car model in the person table.

    So i think im going to have to use redundant data.

    Is this what i should do.

    create a query with the fields "name and car no" from the person table and car model from the other table. then use an append query/update query to re-write the data back to the person table.

    If not, how would i go about it?

    Matt

  4. #4
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Use an update query like this one :

    UPDATE Persons INNER JOIN Cars ON Persons.PersonCarNo = Cars.CarNo SET Persons.PersonCarModel = Cars.CarModel;

  5. #5
    Join Date
    Aug 2003
    Posts
    31

    thank

    Thanks herman

    I will try that tonight when i get home.

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •