Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Posts
    18

    Unanswered: Modify values in a join

    Maybe it's a FAQ, I haven't found it.

    I need to modify values in the Datasheet view of a query based on an inner join among 2 tables.

    At present, this query doesn't let me modify any value.

    How should I alter the table/query/join to accomplish it?

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Clarification

    I'm not sure that I understand your question. Can you get to the datasheet view of the query? Do you have permissions? Are you getting an error when you try and change the datasheet or sql properties? Can you post a copy of it?

    Thanx, Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Sep 2003
    Posts
    18

    Trace

    Quite easily: I create

    Table ALFA: fields ID, a, b, c
    Table BETA: fields ID, a, d, e

    I create a relationship between ALFA and BETA on the common field "a"

    I populate the 2 tables with random values, taking attention to insert the same values for field "a" in some records

    I create a new query with the 2 tables, selecting all the fields. It is automatically created as a join.

    .. in the datasheet view of this query I can't modify any field value..

    I hope it's quite clear now :-)

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Just to make sure...

    You are trying to actually 'update' the data after you run the query. Are you getting an error? I thought you were you trying to actually change something in the query.
    --If its free, take it for what its worth!

  5. #5
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Clear now....

    I just created what you described and it is the same for me. There is a logical reason for this but I'm at a loss right now of how to expain it. I was able to update table Alfa with values from table Beta if that was your ultimate goal.

    :-)Stu
    --If its free, take it for what its worth!

  6. #6
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Resolved!

    You have to make column a in both tables the primary key and then the query will work.

    I knew it was something simple!
    --If its free, take it for what its worth!

  7. #7
    Join Date
    Sep 2003
    Posts
    18

    "a" can't be primary key

    In my DB, column "a" can't be the primary key.. but I'm quite sure that data can be updated directly in the query even if the join field isn't a primary key..

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can only update values in a query if the value to be changed column can be uniquely identified. that usually means it must have the primary key of that row as part of the source.

    However how that works in a data sheet contect I don't know.. I don't use datasheets. If a datasheet style is required I export the data to Excel and let users play to their hearts content in Excel, well away from the source data
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2003
    Posts
    18

    Sample DB

    Well, I've now a small DB (688kb) with 3 tables and 2 join queries.

    In the first query I can modify the extracted values
    In the second one I can't.. that means that the beep sounds when I try to modify the values, and I haven't the New Line at the bottom.

    Is there a kind guy to whom I can send his DB, who could explain me the reason of this behaviour? :-)

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Zip and upload it here...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Sep 2003
    Posts
    18

    Here is it

    Here's the DB: open both the queries, and try to modify there the values..

    ThankYou!
    Attached Files Attached Files

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    Problem 0wn3d by the l33t helpa munky :D

    Interesting.

    This stems from bad table design though, which is very typical... one little mistake in tables and you go and use a query wizard and Access copies the mistake rendering an unexpected result.

    The mistake? DUAL relationships between BETA and GAMMA. When you created the queries using the wizard, Access copies this mistake by creating an inner join with dual criteria. Instead of the "natural" INNER JOIN GAMMA ON BETA.KEY=GAMMA.KEY, access creates INNER JOIN GAMMA ON BETA.KEY=GAMMA.KEY AND BETA.KEY=GAMMA.KEY.

    As soon as I corrected that, the query runs fine.

    Recommendations:
    1. Delete the secondary relationship between BETA and GAMMA.

    2. Modify the SQL of your ALPHAGAMMA query so as it has only one reference to BETA.KEY=GAMMA.KEY.

    3. Always examine your relationships properly with the relationships window, not just chucking a relationships in there "the easy way". Doing things the easy way often leads to the "dark side".

    4. Always enforce your relationships wherever possible. I realise this may not be possible here, but I always recommend this when I see a database relationship window that has a line in it that isn't bold, has a 1 on one end and an infinity symbol on the other ^^

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Sep 2003
    Posts
    18

    Great!

    YOU ARE GREAT! That was actually my problem, not only in this case!

    Really grateful!

Posting Permissions

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