Unanswered: Use Update Query to Update Table from Another Query
I have a date field in CFATABLE that I want to update using a standard query each month. The value will be the maximum date from 13 months data I load each month into another table, which is not directly related to CFATABLE. Can I use a standard query to take the MaxDate from one query, and put it into an update query to fill in the (same) date for all records in the table? I will use the date for reference to know that the next field, which has been updated and the others rotated to each following field (example fields: Month1 Month2 Month3 Month4, etc.) represents information for "Mar 2003", then each field thereafter is known to be one month older. This rotation is done by update query.
My CFATABLE stores several years of history, although only 13 months are reported, and was handed off to me to maintain by setting up new fields each month representing the date, for example: "022003R" "022003V", etc. As these fields are used in queries and reports, it seems like I have spent a major part of my life editing this application every month. My scheme is to use common field names as "Month1", etc. that will eliminate most of the manual editing.
So the question is, can I use a date value from a query and insert that query into an update query to fill in a date field in an unrelated table. I've tried without success. The error message says "Operation must use an updateable query".