Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Exclamation 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".

    Thanks.

  2. #2
    Join Date
    Apr 2003
    Location
    Homer, Alaska
    Posts
    15
    Yes, you should be able to do that.

    In your update query add only the table you're trying to update.
    In the "Update To:" row of the field you're trying to update add the following:
    DLookUp("[MaxDateField]","MaxDateQuery")

    (replace the field & query names with the actual names that are in your database)

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    Thanks for the DLookUp("[MaxDateField]","MaxDateQuery") solution, which allows me to use the results of a query inside of an UPDATE query. It works perfectly.

    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •