Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Updateable query (Access 2000/SP2)

    I have 4 tables, lets call then T1, T2, T3 and T4.T1 has a PK of OrgID, T2 has a PK of GrantID and a FK of OrgID back to T1. T3 has a PK of PayNo (Autonumber) and a FK of GrantID back to T2 and T4 has a PK of GrantID (FK back to T2) and FundingYear.
    T1 to T2 is 1-M.
    T2 to T3 is 1-M.
    T2 to T4 is 1-M.
    I have filters set on T3 and T4 so I get just the one record (T4) but I cannot update a field in T4.
    When I reference all these tables in a query it beeps if I try to update a field in T4. Any ideas on how to fix this.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updateable query (Access 2000/SP2)

    I'm not sure what you're describing, Pat. Are all these tables in the same query? If by "filters", you mean that any of the queries is a group by query, that automatically makes the top level query non-updatable. Fortunately, it isn't too hard to use subqueries in SQL to return the filtering values and then you can make the query updatable. If you post some sample SQL, someone should be able to help you get it working.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Updateable query (Access 2000/SP2)

    <P ID="edit" class=small>(Edited by patt on 23-Jan-04 19:50. Forgot to include the database)</P>Hi Charlotte and anyone else who wants to chip in. I have included a sample database. Try executing the query and try to check the checkbox on the query.

    The query only returns 1 record but this is not enough.
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Updateable query (Access 2000/SP2)

    Looking at the sample db, I'd say main reason query is not updatable is because there is a one-to-many relationship between T2 table and both T3 & T4 tables on same field, GrantNo (PK in T2). If you include fields from both T3 & T4 in query results then query will not be updatable. For an updatable query, use either T3 or T4 for criteria only; do not display criteria field in query results. Modified version of Query1:

    SELECT DISTINCTROW T2.OrgID, T1.Orgname, T2.GrantNo, T4.FundingYear, T4.AccReturn
    FROM ((T1 RIGHT JOIN T2 ON T1.OrgID = T2.OrgID) INNER JOIN T3 ON T2.GrantNo = T3.GrantNo) INNER JOIN T4 ON T2.GrantNo = T4.GrantNo
    WHERE (((T4.FundingYear)=2004) AND ((T3.PayNo)=1));

    This query is updatable. Example that displays records from T3, using T4 for criteria only:

    SELECT DISTINCTROW T2.OrgID, T1.Orgname, T2.GrantNo, T3.PayNo, T3.DueDate, T3.Amount
    FROM ((T1 RIGHT JOIN T2 ON T1.OrgID = T2.OrgID) INNER JOIN T3 ON T2.GrantNo = T3.GrantNo) INNER JOIN T4 ON T2.GrantNo = T4.GrantNo
    WHERE (((T3.PayNo)=1) AND ((T4.FundingYear)=2004));

    See Query2 in attached file, modified version of db. Also note in this case it was necessary to include DISTINCTROW keyword - if you delete this from SQL statement, the queries above will not be updatable.

    HTH
    Attached Files Attached Files

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Updateable query (Access 2000/SP2)

    Thanks Mark
    All I had to do with my query was to add the DistinctRow keyword and it became updateable.
    I notice you had a RIGHT join between T1 and T2, why was that?

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Updateable query (Access 2000/SP2)

    No particular reason, other than I was experimenting with the joins in the query to make it updatable, the Right join can be changed back to inner join & query will still be updatable.

Posting Permissions

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