Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    14

    Unanswered: Update Query issue across tables

    Hi everyone,
    I've create an update query that is meant to update the Student ID on a Life table to make a link between them, after a new student has been created. Both tables have a common field value called 'Lead ID'.
    The update query should find whenever the Values in this field match, and update the Student ID in the Life table (from Student Info) when a check box = false.

    Here is the SQL from the Query, and it doesn't work.

    UPDATE tbl_studentinfo INNER JOIN tbl_studentlife ON tbl_studentinfo.StudentID = tbl_studentlife.StudentID SET tbl_studentlife.StudentID = tbl_studentinfo.StudentID
    WHERE (((tbl_studentinfo.sinfoLeadLife)=False) AND ((tbl_studentinfo.sinfoLeadID)=[tbl_studentlife]![sleadID]));
    Any help or suggestions would be appreciated, thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    This query isn't going to do anything.

    Your SET statement sets the ID of one table to match the value in the corresponding field on the other table, but you're joining the tables on that field - the two values must already match for the query to find them.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2010
    Posts
    14
    Yeah I can see that now, because that's the field the inner join is on. I'll have to go about it another way. Thanks

Posting Permissions

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