avoid non-updateable query (2003)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote