Results 1 to 4 of 4

Thread: Access subQuery

  1. #1
    Join Date
    Jun 2002
    Posts
    3

    Unanswered: Access subQuery

    I'm trying to do an UPDATE query using 2 subqueries (which I don't understand very well). I am getting a "Operation must be an updateable query" error with the following statement:

    UPDATE schedule SET description="Hello", jobID=(SELECT jobs.ID FROM jobs WHERE jobs.Name="Orange Factory") WHERE (description="old description" AND userNum=(SELECT ID FROM users WHERE name="Tina"))

    Is this statement the correct way to go about this problem & what is wrong with it?

    Thanks!

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    This statement

    userNum=(SELECT ID FROM users WHERE name="Tina"))

    How can a userNum = an ID Number which is filtered on the name Tina?

    WHERE (description="old description"

    Does your field description contain an entry called old description?

    SET description="Hello"

    How can description be set to Hello and equal old description in the same query?

    To be of most benefit to you try writing out in Word or Notepad what you would like the result of your query to look like, then zip it and your database and post to this forum. Somone will solve it and return it, then you can look at it and make small changes to the queries to at least get a better idea as to what is happening.

  3. #3
    Join Date
    Jun 2002
    Posts
    3
    The database I'm using is attached...

    What I'm trying to do is an UPDATE query which will update schedule.usernum and schedule.description. In order to update schedule.usernum, I would like to be able to submit a name from users.name and have the database look up the corresponding ID to UPDATE the schedule.usernum to.

    Hope this makes sense... Thanks again!
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    I have made some changes to the database.

    1: I have made your query work as requested.

    2: I have added some more tables, the same data just structured better, they are preceedced by tbl.

    3: I have added some forms which update the updated table in the manner I think you really intend the update done.

    When you download, try this out by first selecting the update query and running it, to update your table schedule. You can then see the results of an update query.

    Then try adding a new job to tblJobs from the form frmJob.

    Then assign a person to the job through frmUpdateSchedule.

    Then view the results in tblSchedule.

    HTH

    Attached Files Attached Files

Posting Permissions

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