Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query Error (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query Error (2000)

    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)

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query Error (2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •