By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Using a query within a query

P: 60
Hi,
I'm trying to use the results from a select query [selects most recent count for each count station] as a way to limit the records that I update with my update query and it seems like it should work but I keep getting a "Operation must use an updateable query" error. Essentially the entire update query works fine until I add the select query as a way to limit the records. If I replace the select query with an actual table of results from the select query it works fine but I prefer the select query method as it will always give me the most current information and not a picture frozen in time. The select query on its own works fine as well so it likely has to do with combining these two queries. Any help would be greatly appreciated! Thanks.

I've posted my SQL statement below

UPDATE [Selects most recent count for each count station] INNER JOIN ((Counties INNER JOIN (Count_Stations INNER JOIN Traffic ON Count_Stations.PCID = Traffic.PCID) ON Counties.CNTY_FIPS = Count_Stations.CNTY_FIPS) INNER JOIN Growth_Rates ON Counties.CNTY_FIPS = Growth_Rates.CNTY_FIPS) ON [Selects most recent count for each count station].MaxOfTRAFFICID = Traffic.TRAFFICID SET Traffic.YEAR_FACTOR = IIf((1+growth_rates![2007])*(1+growth_rates![2006])*(1+growth_rates![2005])*(1+traffic!pre_05_growth_factor)^(2005-DatePart("yyyy",traffic!count_date))<=1.15,(1+grow th_rates![2007])*(1+growth_rates![2006])*(1+growth_rates![2005])*(1+traffic!pre_05_growth_factor)^(2005-DatePart("yyyy",traffic!count_date)),1.15), Traffic.YEAR_FACTOR_SOURCE = "DM pre 05 EPA post"
WHERE (((DatePart("yyyy",[count_date]))<2005));
Oct 28 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
Hi ramprat. Access is quite limited in what it considers updatable - and any use of an aggregate function, such as the Max I guess you are using in your underlying SELECT query, renders the resultant query non-updatable.

One way to work round this is to perform the update in two stages (which you mention in your post). The first stage is to run a make-table query based on the SELECT statement to create a temporary table to hold the values you want for the update, and the second stage is to join the temporary table to the actual table you want to update then run the UPDATE from the joined temp and actual tables (table-to-table joins are always updatable, unlike table-to-query joins).

This can be automated as an Access macro to keep the two stages running in correct sequence from the one command, and doing so means that you are always using up-to-date data for the update.

-Stewart
Oct 30 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.