Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query (Access 97)

    Hi

    I have the following query called Active QueryAll:

    SELECT DISTINCTROW Active.Active_ID, Sum(Dietary_Figures.Dietary_NZ*NEDI.STMR)/1000 AS NEDI_Aver
    FROM (Crop INNER JOIN (Active INNER JOIN NEDI ON Active.Active_ID = NEDI.Active_ID) ON Crop.subject_id = NEDI.SubjectID) INNER JOIN Dietary_Figures ON Crop.subject_id = Dietary_Figures.Subject_ID
    GROUP BY Active.Active_ID;

    My question is can I use this query to update another table - Active with the summary figure of NEDI_Aver in the above query into the field NEDI_Average for corresponding active_ids in the Active table via an update query. If so, could someone please give me a clue on the correct sql syntax to use in the query.

    Thanks & Regards
    WTH

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Query (Access 97)

    Yes you can!
    I find it easier to build it with the qbe grid than writing the sql from scratch.

    Create a new query in the qbe grid, and add the table Active and this query . Join them on active_id if they are not joined automatically. In the menus, go to query, then select update . The query grid will then get an update to line.
    Add the field [NEDI_Average] to the grid, then in the update to line put the name of your summary field from the grouping query, enclosed by [ ].
    You can then view the sql, or run the query by clicking the ! button. Be careful though there is no undo. (i.e. make a backup first)
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query (Access 97)

    Hi

    Thanks for the reply. If I follow you correctly, then I get the following sql:

    UPDATE Active INNER JOIN [Active QueryAll] ON Active.Active_ID = [Active QueryAll].Active_ID SET Active.NEDI_Average = [Active QueryAll].NEDI_Aver;

    However, on running it, I get the following error message:

    Operation must use an updatable query

    Any ideas on why I get this error message.

    Regards
    WTH

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Query (Access 97)

    Your SQL looks OK to me. I have tried to do a similar thing to test what is going on and I get the same error as you do.
    I thought this would work!
    Grouping queries are not updateable, and when you include one within another query it makes the whole thing non updateable.
    One solution would be to have a temp table with just two fields Active_ID and NEDI_active , and turn your first grouping query into an append query. Then use the temp table for the update instead of the query. You would then need a delete query to clear it out again.
    Regards
    John



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

    Re: Update Query (Access 97)

    Try the following query:

    UPDATE Active SET NEDI_Average = DLookUp("NEDI_Aver","Active QueryAll","Active_ID=" & [Active_ID]);

    I have assumed that Active_ID is numeric; if it is a text field, use<pre>"Active_ID='" & [Active_ID] & "'"</pre>

    as Where-condition in the DLookup.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query (Access 97)

    Hi

    Yes that works thanks alot, although I did solve it by another method using VBA code. However, your code will come in handy in the future.

    Regards
    WTH

Posting Permissions

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