operation must use an updatable query (1 Viewer)

G

gutmj

Registered User.

Local time

Today, 03:12

Joined

May 2, 2008

Messages

26

• Jul 14, 2011

• #1

Hi,

I am executing a simple update query:

UPDATE tblMatrix INNER JOIN qrySelectMaxDate ON tblAll.SoldTo = qrySelectMaxDate.SoldTo SET tblMatrix.LastInvoicedDate = [qrySelectMaxDate]![MaxOfInvoiceDate];

and I am receiving an error "operation must use an updatable query".
Googled it but not of the explantation seems to be aplicable to my query.

Any thoughts?
Thanks

 

E

elliotgr

Registered User.

Local time

Today, 05:12

Joined

Dec 30, 2010

Messages

67

• Jul 14, 2011

• #2

This occurs when there is a many to one relationship.
The best way to resolve is is to use a query to generate the update data table, then use the update data table to update the original table.

 

spikepl

Eledittingent Beliped

Local time

Today, 04:12

Joined

Nov 3, 2010

Messages

6,142

• Jul 14, 2011

• #3

which record in tblMatrix is supposed to get updated?

 

G

gutmj

Registered User.

Local time

Today, 03:12

Joined

May 2, 2008

Messages

26

• Jul 14, 2011

• #4

Great, thanks a lot. Followed your advice.

 

E

elliotgr

Registered User.

Local time

Today, 05:12

Joined

Dec 30, 2010

Messages

67

• Jul 14, 2011

• #5

Change qrySelectMaxDate to a make-table and run it. Call the new table tbl_SelectMaxDate

Then change your original query to UPDATE tblMatrix INNER JOIN tbl_SelectMaxDate ON tblAll.SoldTo = tbl_SelectMaxDate.SoldTo SET tblMatrix.LastInvoicedDate = [tbl_SelectMaxDate]![MaxOfInvoiceDate];

Now it should run.

 

G

gutmj

Registered User.

Local time

Today, 03:12

Joined

May 2, 2008

Messages

26

• Jul 14, 2011

• #6

Sorry, i wasn't clear. Thanks elliotgr, i've done that and it worked fine. Thanks for your help