Updateable Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created the following Query

UPDATE ACCT
SET ACCT.DT_EQ_1STREP = (SELECT Max([DT_Report]) AS MAX_DATE
FROM REPORTS
WHERE
[REPORTS].[ACCT_ID] = 2 and [REPORTS].[AGENCY] = "EQ";)
WHERE (((ACCT.ID)=2));

When I try to run the query I get an error message "Operation must use
updateable query"

The HELP details that are shown on that seem to apply are as follows -

You attempted to run a query that tried to update a field that cannot be
updated. For example, you may have created the query in such a way that you
tried to update a field on the one side of a one-to-many relationship.

I have some background in SQL - the field I am trying to update is a date
field.

The MAX function is being used to retreive the value to be used for update
from another table.

Any ideas on what the problem might be?

Note: The ACCT Table is the ONE side in a One-To-Many relatioship with the
REPORTS table
 
Jet sees the Totals query in the subquery and then assumes that the entire
query cannot be updated.

Use a DMax function instead of the subquery:

UPDATE ACCT
SET ACCT.DT_EQ_1STREP =
DMax("DT_Report", "REPORTS","[ACCT_ID] = 2 and [AGENCY] = 'EQ'")
WHERE (((ACCT.ID)=2));
 
Back
Top