Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    avoid non-updateable query (2003)

    In the table called GPSPos, there are two columns named GridLongitude and GridLatitude (LongInt). I want to find all the pairs of these two fields that occur more than a specified number of times (e.g. 10 times) and mark the records where they occur with an arbitrary value in the field called ZoneIDfk. So I created a query named qryGridLongAndLatWithHighCount with the following SQL:

    SELECT GPSPos.GridLongitude, GPSPos.GridLatitude, Count(GPSPos.RecNum) AS CountOfRecNum FROM GPSPos
    GROUP BY GPSPos.GridLongitude, GPSPos.GridLatitude
    HAVING (((Count(GPSPos.RecNum))>10));

    This query returns all the correct records.

    Then I joined that query back to the original GPSPos table, and attempted to update the ZoneIDfk field:

    UPDATE GPSPos INNER JOIN qryGridLongAndLatWithHighCount ON (GPSPos.GridLongitude = qryGridLongAndLatWithHighCount.GridLongitude) AND (GPSPos.GridLatitude = qryGridLongAndLatWithHighCount.GridLatitude) SET GPSPos.ZoneIDfk = 1;

    Access issues an error message that the process must be based on an updateable query. I know I can workaround this problem by creating a temporary table instead of using qryGridLongAndLatWithHighCount , but I would like to avoid doing that if possible.

    Can anybody suggest a different strategy to use straight SQL to accomplish this task, without resorting to a temporary table?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: avoid non-updateable query (2003)

    I doubt you can do it with straightforward SQL statements. Access will insist that both tables (it treats the group-by query as a table) have primary keys, and queries in general do not inherit the primary key values from the base table. So a temporary table is usually the most expedient method. You might be able to create a subquery, much as the Find Duplicates wizard does, that would do the trick, but they are always tricky things to do - and I spend hours working out the SQL when a few minutes would do the job with a temporary table.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: avoid non-updateable query (2003)

    Thanks Wendell. I've gone ahead and implemented it with the temporary table. I've done the subselect routine when there was only one field involved, but the inclusion of two fields had me stumped. Thanks for the confirmation that I wasn't missing anything obvious.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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