Update another table with a Max record query

[Ngan]

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

 

[Guest]

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

 

[Ngan]

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