Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: update query question

    I have an update query question. Here is the table I am working from:


    ProjectID MonthNumber ApprovedStatus
    A 14 1
    A 15 (Null)
    B 14 1
    B 15 (Null)
    C 14 0
    C 15 (Null)
    D 14 1
    D 15 (Null)
    E 14 0
    E 15 (Null)
    F 14 0
    F 15 (Null)



    I am trying to use an update query to change APPROVEDSTATUS to 1 (or 0) for MONTHNUMBER=15 if APPROVEDSTATUS=1 for MONTHNUMBER=14 (for the same projectID)

    I'm sure it's pretty easy but I seem to be coming up empty.

    Any suggestions?

    Thanks, Norm

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ehhh..

    You just said it has to be MONTHNUMBER 14 and 15 at the same time. That's not going to work.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Dec 2003
    Posts
    268

    Vba

    I think your best bet would be to approach this from a coding prespective

    dim db as database: set db = currentdb
    dim rs as recordset: set rs = db.openrecordset("SELECT ProjectID FROM tblYourTable WHERE ProjectMonth = 14 AND ApprovedStatus = 1")

    if rs.absoluteposition = -1 then exit sub
    while not rs.eof
    db.execute("UPDATE tblYourTable SET ApprovedStatus = 1 WHERE ProjectMonth = 15 AND ProjectID = '" & rs.fields(0) &"'")
    rs.movenext
    set rs=nothing


    This gives you a dataset that is just those projects in the 14th month that were approved.

    It then looks at the same table for those projects that have the same project ID and updates the approved status to 1 where the project month is = 15

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh... is that it? You want to set the approved status to 1 for month 15 where month 14 is already approved?

    UPDATE yourTable
    SET approvedStatus = 1
    WHERE MonthNumber = 15 AND EXISTS (SELECT * FROM yourTable t1 WHERE t1.ProjectID = yourTable.ProjectID AND ApprovedStatus = 1)
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Arent you missing AS? I got an error when I try and leae AS out

  6. #6
    Join Date
    Jan 2004
    Posts
    100
    You definitely have the right idea but it seems to be changing all entries of month15 to a 1 regardless of what APPROVEDSTATUS is for month14. The strange part is that the SELECT statement with the EXISTS statement pulls in the correct group that should be changed.

    Quote Originally Posted by Teddy
    Oh... is that it? You want to set the approved status to 1 for month 15 where month 14 is already approved?

    UPDATE yourTable
    SET approvedStatus = 1
    WHERE MonthNumber = 15 AND EXISTS (SELECT * FROM yourTable t1 WHERE t1.ProjectID = yourTable.ProjectID AND ApprovedStatus = 1)
    Last edited by norm801; 07-16-04 at 10:33.

Posting Permissions

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