SELECT query within UPDATE query (VBA) - Access 2003 (1 Viewer)

Hi everyone, hoping you can help! Rapidly running out of hair to pull out....

I'm trying to update a field in a table of top 20 manufacturers for 7 managers with their respective rank, using the "rank" select statement from the Microsoft website (select (count(*)) etc.) Access is coming up with an error saying I must use an updateable query. Is there a workaround to this? I've read solutions to similar problems elsewhere that include using DLOOKUP, but the information doesn't exist in another table, just as a calculated field in my update query.

The Top 20 table is populated with a for loop that selects top 20 manufacturers based on manager ID number (1-7), this works fine and I have a 140-row table showing manager ID 1-7, and their respective top 20 manufacturers. What I wanted to do was create another for loop that updated the rank field with the select count * statement, where manager ID was equal to the iteration of the for loop. Is this possible?

Thanks in advance!

J

The only way I know is to append the calculated data into a temporary table and then Update from there

Thanks very much for your reply David, I'll give that a go.

J

It's frustrating, you know exactly what you want to do, but the rules of Access stops you doing it. But I guess it makes sense, if you do a Group By, you could get 1000 results that meet the criteria, how is Access to know exactly which of these records you want to update

Tell me about it! The joys of MS software.... keeps us busy though eh?

I've managed to achieve the result I wanted by using for loops and make table queries rather than updates - one to create a table without ranks, then another to make a table with that table and the rank... time consuming but it works!

Thanks again for your help!

Don't lose heart, we are competing with fully trained sql / vba programmers trying to pick up what we can from incomrehensible help screens and not much of an idea of what can be achieved with wizards (if only somebody would explain in English, what it all means)

Keep battling & exploring, it's worth the effort, for no other reason than it stretches the brain cells

Just Keep Swimming is my motto! And I bet that most of the "fully trained" programmers refer to you guys as much as we noobs!

As an aside I found that running the SQL code from a module does the job much more quickly. Very strange. But it works so I'm not complaining....