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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update another table with a Max record query
Last week, I asked about how to get a max record. Thanks
to those who helped me out. However, I forgot to explain the real problem, using the max record query in an update query. I have a tblElig where a Client can have more than one Elig record. I want to get the most current Elig record (max of EffectiveTo date field). Here's what I did: The first query (qryMaxEffTo) is a Totals query that finds the Max(EffTo) for each ClientID (GroupBy). The second query (qryMaxElig) has the first query joined/linked to the underlying table (tblElig) by ClientID and EffTo fields. I add EligStatus to the second query to get other info. Now I have a update query (qryUpdateClient) where I have qryMaxElig and tblClient where ClientID and MaxEffTo in qryMaxElig is joined to ClientID and EffTo in tblClient. The update field would be EffTo and EligStatus of tblClient. The "Update To" field would be the MaxEffto and EligStatus of qryMaxElig. When I try to run the update query, it says "Operation must use an updatable query". I read the help file on that. It says that it happens when the current query's update to field includes a field from a select query in which an aggregate (total) was calculated. I need to do this update for all the clients, so in the update to field, I can't have a DMax ("effto", "qryMaxElig", "ClientID=######") and do a loop... It'll take forever to run. Is there a way to do this? Thanks. Ngan |
#2
|
|||
|
|||
Update another table with a Max record query
I don't understand your reference to a "loop"
"I can't have a DMax ("effto", "qryMaxElig", "ClientID=######") and do a loop... It'll take forever to run. You should be able to use DMax ("effto", "qryMaxElig", "ClientID=" & [ClientID field name]) another option is to use totals query to make a temp table, and then use that in your update query |
#3
|
|||
|
|||
Update another table with a Max record query
What I meant was I need to have the query update all the
records. How would I do that with the DMax function? In either case, someone mentioned that the query would be un-updatable because I have the PK in the query. I did go with the option of having the totals query make a table and then use that table in the link for the update query. I was just wondering if there was any other option that didn't require using the temp table. Thanks! Ngan -----Original Message----- I don't understand your reference to a "loop" "I can't have a DMax ("effto", "qryMaxElig", "ClientID=######") and do a loop... It'll take forever to run. You should be able to use DMax ("effto", "qryMaxElig", "ClientID=" & [ClientID field name]) another option is to use totals query to make a temp table, and then use that in your update query . |
Thread Tools | |
Display Modes | |
|
|