Results 1 to 7 of 7

Thread: SQL Sintax

  1. #1
    Join Date
    Jan 2005

    Question Unanswered: SQL Sintax

    I have this 2 Tables



    I want to get the CompanyName into the VisitorCompany, having the criteria VisitorCompanyId=CompanyId

    I've Tried This:
    UPDATE Visitors, Companies SET Visitors.VisitorCompany = Companies.CompanyName
    WHERE (((Visitors.VisitorCompanyId)=[Companies].[Company_Id]));

    but it isn't working.... what is wrong!!!
    Last edited by santal_maluko; 01-07-05 at 10:53.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Why update the table at all?
    if you have enforced the relation between visitorcompanyID and CompanyID at the time the visitor row was created then why store the companny name here aswell. Unless you are concerned that the visitorcompany name could change or become detached from the associated company.

    Providing the datatype of the 2 columns is defined to tbe the same (ie same datatype OR ComanyID Autonumber and VisitorCompanyID Long) >note you cant', or at least shouldn't have both of type autonumber

    When you define your relationship between the two tables this will keep the data in sync. Define company as the master (one) and visitorCompanyID as the child (many) so you defien a one to many relationship. This equates to suggesting that a visitor may come from a company, and that you may get many visitors from that company over time. The only pitfall is if you get visitors who may represent many companies (depending on the role or purpose of their visit). Is supect for the purposes of your db this is irrelevant).

  3. #3
    Join Date
    Jan 2005
    this is for a program that uses a database....but the new version of it has same old database incompatibility... one is this....
    I don't want to have the visitors associated to the companies anymore...
    so, I'm doing this in the update database program
    1st - create a new column called VisitorCompanyId
    2nd - copy all the data in VisitorCompany to VisitorCompanyId
    3rd - Drop and create as TEXT the visitorCompany
    and finaly get the data from the table companies in order to drop the table in the next step

    So do you know what I am missing with the Syntax?

    Note: the indexes are
    VisitorId for Table Visitors
    CompanyId for Table Companies
    Last edited by santal_maluko; 01-07-05 at 10:51.

  4. #4
    Join Date
    Sep 2003
    Caldes de Malavella, Spain
    In the Companies table, the field is called "CompanyID" but in your SQL you've called it "Company_ID".
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy

  5. #5
    Join Date
    Jan 2005
    I've already check this... I have mistyped but I corrected and it is still not working

  6. #6
    Join Date
    Jan 2005
    guess it was all correct.... I was stupid like a stone, and I was not running the SQL.... at least we talked a little bit
    thanks for verything

  7. #7
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Quote Originally Posted by santal_maluko
    guess it was all correct.... I was stupid like a stone, and I was not running the SQL.... at least we talked a little bit
    thanks for verything
    Stones are not without their redeeming qualities my friend. Sometimes it just takes a fresh approach.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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