can't use nonupdateable query in update query even when not updating the nonupdateabl

[davefwelch]

I had this bit of my project done, until I realized that the entire project was so large that I was going to have to instead create a table that has been updated with data as the basis for my report.

I am accessing another database via ODBC. This database contains many many tables, but at this point I am working only with two. TINWSYS contains a record for each municipal water system in my state, and TINWSF contains the data regarding the different sources that each system has. Some systems have one source, some have two, three, four, etc. And there are three different sources: Wells (WL), Intakes (IN - these are surface water sources, mostly lakes), and Consecutive Connections (CC - this is where two proximal water systems have a connection to each others distribution, like one is purchasing water from the other).

Like I said, I had created a query that did a terrific job of summarizing a description of all the sources that each water system had, which will be part of a several-page report for each water system (there are over 380 systems). But when I tried to change that query to one that would simply update a text field with that source description, I get the good old "not an updateable query" error.

So I started by breaking down the original query so I could instead build the source description in the report table. First step: how many wells does each system have, if any? But when I create an update query that has this SQL, it updates each record with 611 (the TOTAL number of wells for all 380+ water systems).

UPDATE CCRsystems SET CCRsystems.SourceDescription = "wells: " & DCount('[PWSID]','[qrySourceType]','[qrySourceType]![SourceType] ="WL"')
WHERE ((DCount("[NAME]","[qrySourceType]","[qrySourceType]![SourceType] ='WL' ")>"0"));

Any help would be appreciated

 

[Galaxiom]

Use an Aggregate (Totals) query. Group By the system name and source type then add a count field. This will return a count for each source type for each system.

 

[davefwelch]

Thanks for the quick reply, Galaxiom, but that was my first attempt. The problem with that is that the Aggregate query is nonupdateable. I think I am going to try loading necessary data from the ODBC read-only tables into a temporary table using an append query.

Anyone have any other ideas for me?