Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2006
    Posts
    5

    Unanswered: "operation must use an updatable query"

    Did some searching, but didn't find a solution to my problem.

    I'm trying to update a table using a select query as my data source. In this select query, I've calculated a field and that field's data is what I want to update in another table. If I make the select query data into a table itself and then use that table as the data source, it works, but that seems an otherwise useless step and it adds clutter to my tables. Is there a way to do what I'm trying to do or am I stuck with the make-table-query -> table -> update-query -> other-table?

    Thanks for the assistance.

    Emmie

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You shouldn't have to do that, depends on what you tried for a SQL statement...
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Nov 2006
    Posts
    5
    I'm trying to use 1 select query (qselCurrFAMData) and 1 table (tblReturnedToCFI_Students) as my data sources. The table I'm attempting to update is tblAllCompareRecs. Now, to give you the complete story, the select query I'm trying to use as the source is receiving its fields from yet another table and another select query in which I do a sum and grouping to calc [TotalEPD]. In this final select query, though, there should still be a 1-to-1 match between the records in qselCurrFAMData and the records in the table I'm trying to update based on the 4 joined fields.

    I'm using the query Design View, but here's what that puts out in the SQL View...

    UPDATE (tblAllCompareRecs INNER JOIN qselCurrFAMData ON (tblAllCompareRecs.PID = qselCurrFAMData.PID) AND (tblAllCompareRecs.FAMYr = qselCurrFAMData.FAMYr) AND (tblAllCompareRecs.GrantType = qselCurrFAMData.GRANT) AND (tblAllCompareRecs.EnrPeriod = qselCurrFAMData.Term)) INNER JOIN tblReturnedToCFI_Students ON tblAllCompareRecs.PID = tblReturnedToCFI_Students.PID SET tblAllCompareRecs.TotalEPD = [qselCurrFAMData]![TotalEPD], tblReturnedToCFI_Students.CkAutoReqID = tblReturnedToCFI_Students!CkAutoReqID;

  4. #4
    Join Date
    Jul 2005
    Posts
    39
    Couple of questions: Are you trying to update the tblReturnedToCFI_Students!CkAutoReqID with itself? and are the tables in an ERP?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can't update a query that uses a join.

    What's your purpose for storing derived data anyways?
    oh yeah... documentation... I have heard of that.

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

  6. #6
    Join Date
    Nov 2006
    Posts
    5
    Quote Originally Posted by taurus
    Couple of questions: Are you trying to update the tblReturnedToCFI_Students!CkAutoReqID with itself?
    No; trying to update that same field name from tblReturnedToCFI_Students to tblAllCompareRecs.

    Quote Originally Posted by taurus
    and are the tables in an ERP?
    No.

  7. #7
    Join Date
    Nov 2006
    Posts
    5
    Quote Originally Posted by Teddy
    You can't update a query that uses a join.
    You mean I can't update a table with data from a query? If I replace the qselCurrFAMData with one of the underlying tables, the update runs fine being joined to the 2 tables; just not when joined to the query.

    I think it must have to do with the calculations being performed behind qselCurrFAMData. As a test, I created a select query without calcs, replaced qselCurrFAMData with that query, and it ran fine. If that *is* the problem, that's kind of a bummer.

  8. #8
    Join Date
    Jul 2005
    Posts
    39
    Maybe I'm misreading your sql which has in the SET ...
    tblReturnedToCFI_Students.CkAutoReqID = tblReturnedToCFI_Students!CkAutoReqID;
    shouldn't LHS refer to the destination table?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by emmie
    ...You mean I can't update a table with data from a query? .....
    you can if the query contains all the key and required columns in both tables

    ie including both sides of the join
    if the join is <table1>.colID = <table2>.colID

    then your select must include
    select <table1>.colID, <table2>.colID, <other columns as required>

    likewise it must include all required columns, or thiose with other forms of contraints imposed on them
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2006
    Posts
    5
    Quote Originally Posted by taurus
    Maybe I'm misreading your sql which has in the SET ...
    shouldn't LHS refer to the destination table?
    If I can figure a way to provide you with an image from the Design View, I'll provide that. I'm not a SQL writer so I can't say exactly what the SQL *should* look like. As I say, I created this query in Design View and just brought up the SQL View and copy/pasted it here for y'all to see.

    I was out of the office today and have some meetings tomorrow, but if I can make the image thing work, I'll post a link or something over the next few days.

    Either way, thanks to all who've been willing to try to help.

Posting Permissions

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