Update Query Error (2000)

Topic

New Reply

I am trying to run an Update Query; however, it’s returning “Operation must use an updateable Query” and I’m not sure why.

First a little background: I am using two tables – 1.) DATAtbl, containing all invoice information for each customer, and 2.) CUSTtbl, containing all customer information. CUST_ID from the CUSTtbl is looked up in the DATAtbl CUST_ID field, i.e. CUSTtbl records are one to the many DATAtbl records.

I created 3 queries – 2 select and 1 update – for my Black List report, i.e. all customers who have not settled their invoices after 30 calendar days.

Query 1 – BLACK_LIST_AGE_1qry (Select) takes it’s data from both the DATAtbl, CUSTtbl and has one exrpression to calculate the age for each item.
CUST_ID, CUST_NAME & BLACK_LIST (YES or NO Value) are taken from CUSTtbl;
SETTLED (YES or NO Value), NOT_DATE & SETTLE_DATE are taken from the DATAtbl.
The Age calculation: Age: IIF(IsNull([NOT_DATE]),””IIf(IsNull([SETTLE_DATE]),Date()-[NOT_DATE],[SETTLE_DATE]-[NOT_DATE]))
The Total field is set to Group By for all except, Age, which is set to Expression.
There are no criteria settings for this query.

Query 2 – BLACK_LIST_AGE_2qry (Select) takes its data from BLACK_LIST_AGE_1qry. The purpose of this query aggregate and filter the data from the first query.
SETTLED Criteria: “NO”
NOT_DATE Criteria: Is Not Null
SETTLE_DATE : Is Null
Age: >”30″

Query 3 – BLACK_LIST_AGE_3qry (Update) takes its data from BLACK_LIST_AGE_2qry – there is only one value: BLACK_LIST, with the Update To: set to “YES”.

Any suggestions?

Thanks!

Scott

Reply | Quote

Replies

A totals (group by) query is never updateable, since it combines multiple records into one. Therefore an update query based (directly or indirectly) on a totals query won’t work.

Do the following instead of yout 3rd query:
– Create a new query based on DATAtbl.
– Add the CUST_ID and BLACK_LIST fields to the query grid.
– Enter the following in the Criteria line under CUST_ID:

In (SELECT CUST_ID FROM BLACK_LIST_AGE_2qry)

– Select Query | Update Query.

Enter Yes (or True) in the ‘Update to’ line under BLACK_LIST.

The SQL for this query is (more or less):

UPDATE DATAtbl SET BLACK_LIST = True WHERE CUST_ID In (SELECT CUST_ID FROM BLACK_LIST_AGE_2qry)

Reply | Quote

Thanks Hans for the explaination and the solution – it worked great. I’m kicking around the idea of updating this field dynamically by using an IIf statement in the control source of the field. If I do and get stuck, I’ll probably submit another question.

Thanks for your help.

Scott

Reply | Quote