If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Thank you. I also figured out that the append query works, but ultimately I would like table2 to contain about 15 columns (still only 1 row), each storing it's own value for future reference. Which is why I would like to use the Update query to continue updating each of the 15 columns as roll through my data-manipulation steps.

I read up on Updatable Queries, but to be honest it didn't make much sense. Can you just give me a quick explanation as to why MS Access thinks I need an updatable query for this?

The 1-2 punch sequence I gave you will work, as it produces an updateable query. Try it, you'll like it.

To explain briefly. In general, Access only allows data to be modified in tables, nowhere else. In non-totals queries that don't contain duplicate records, also, Access will allow it, as there is no - or low - possibility to corrupt data. However, in a query that contains duplicate records, or a totals query (a query that uses any aggregate function, such as Sum() in your case, is a totals query) that acts on multiple records (Sum(Col1), for example) creates a non-updateable query, as there is a very high possibility of corrupting data. Access will not allow this to run in a DELETE or UPDATE query.

Ergo, when your original UPDATE query called for SET Col1 = (SELECT Sum(Col1...., etc, you were doing exactly this. However, when you first create the interim table using a Make Table query, that was allowed, because Access allows a totals query in a MAKE TABLE or APPEND query, as the query is not modifying existing data, it is simply adding new records and new data which can be hunted down and deleted if necessary.

Once you have the interim table, you no longer need an aggregate function to do your UPDATE query, as I showed you the exact code for that query. Just be sure to delete the table in between runnings of the program.

Of course, your Make Table query can work for multiple columns as well. Your UPDATE query will simply SET each column of Table1 based on the contents of a different column in the interim table.

Rather than having to use the MAKE TABLE query each time I want to 'copy' a calculated data value from one of my tables into a repository table, would it be easier/possible to simply APPEND the calculated value to a temporary table and then use the next query to take the last value/row in the temporary table and insert it into my repository table. This would in essence be using the temporary table as a half-way-point to the final repository table.

You don't want to do that, because Access doesn't always store the last appended record in a table as the last record. Access uses a complicated algorithm to calculate where it wants to store a record.

Besides, you can automate the deleting of the previously-made table, re-running the Make Table query, and the UPDATE query, all from within a macro or VBA.

Rather than having to use the MAKE TABLE query each time I want to 'copy' a calculated data value from one of my tables into a repository table, would it be easier/possible to simply APPEND the calculated value to a temporary table and then use the next query to take the last value/row in the temporary table and insert it into my repository table. This would in essence be using the temporary table as a half-way-point to the final repository table.

?

Being as you're not actually keeping (storing) your data permanently, why not just have a Query / Form calculate it on the fly, spit it out in a Report and be done with it? Creating a Temp Table seems to be just a step not really needed as it seems once you calculate it and send it to the table, you are essentially done, until the next time for which it seems you are Deleting that last update and replacing it anyway. Just trying to reason the logic in it is all.