Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updateable Query (Access 2000)

    Why pray tell would this not be an updateable query.

    UPDATE tblCorporateRecruiting
    INNER JOIN qsCountNew ON (tblCorporateRecruiting.requisitionMonth = qsCountNew.createMonth)
    AND (tblCorporateRecruiting.requisitionYear = qsCountNew.reportYear)
    SET tblCorporateRecruiting.requisitionNewCount = [CountOfrequisitionNumber];


    When run as a SELECT query, one and only one row is returned.
    The query that is used in the join returns only one row as well.


    Any insight would be greatly appreciated .
    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updateable Query (Access 2000)

    Is there a unique index on the combination of month and year?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updateable Query (Access 2000)

    No, but the way the table is built, there can only be one Month/Year combination.
    Richard

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updateable Query (Access 2000)

    Does tblCorporateRecruiting have a primary key, and if so, what is its name?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updateable Query (Access 2000)

    It does have a primary key, name ID, that is an autonumber filed.

    I added the unique index on the year/month combination, but the query remains not updateable.
    Richard

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updateable Query (Access 2000)

    OK, try this:

    UPDATE tblCorporateRecruiting AS t
    SET t.requisitionNewCount = [CountOfrequisitionNumber]
    WHERE t.ID In (SELECT tblCorporateRecruiting.ID FROM tblCorporateRecruiting INNER JOIN qsCountNew ON (tblCorporateRecruiting.requisitionMonth = qsCountNew.createMonth) AND (tblCorporateRecruiting.requisitionYear = qsCountNew.reportYear))

    The criteria have been moved to a subquery.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updateable Query (Access 2000)

    That let me make the update, but it treats the [CountOfrequisitionNumber] as a parameter. It should be pulling it from the query qsCountNew.
    Richard

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updateable Query (Access 2000)

    This is getting too hard to visualize without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updateable Query (Access 2000)

    Here it is.
    Richard

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updateable Query (Access 2000)

    Hopefully, this will work (it does in the stripped down version):

    UPDATE tblCorporateRecruiting SET tblCorporateRecruiting.requisitionNewCount = DLookUp("CountOfrequisitionNumber","qsCountNew","c reateMonth = " & [requisitionMonth] & " AND reportYear = " & [requisitionYear]);

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updateable Query (Access 2000)

    I will try that. If I cna get it working it may be cleaner than what I came up with.

    I came up with a different work around.
    I created a tblTempResults.
    The results of the counting query are placed in that table.
    That table is then used in the update query.

    Kind of the long way round, but it works.


    Thanks for all your help.
    Richard

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updateable Query (Access 2000)

    Creating a temporary table is one of the possible workarounds for non-updateable action queries.

Posting Permissions

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