Results 1 to 4 of 4
  1. #1
    toothpaste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9

    Editing entries in a query

    First, I have the following tables:

    activities:


    ID Guide Garden activityDate comment

    gardens:
    ID garden_num

    activeGuides:

    ID first_name last_name

    I have form or a query that is based on the following SQL query:

    Code:
    SELECT activities.ID,
    [first_name] & ' ' & [last_name] AS Guide, 
    gardens.garden_num, 
    activities.activityDate, 
    activities.comment
    FROM activities 
    INNER JOIN activeGuides ON activities.guide = activeGuides.ID
    INNER JOIN gardens ON activities.garden = gardens.ID
    The problems happens when I try to change the garden_num of a certain activity, not only that garden_num changes, but to other activities!
    I do not see any constancy I could point as to which activities do change or which do not.
    I'd be grateful if someone could point me out to how to edit only one activity.

    Thanks!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    since you are joining on the ID, if you change the garden_num field value, all activities that are linked to that ID will change as well. What you should be changing is the garden value in the activities table.

    in a form the rule is one table one form. So your from should be based on the activities table only and the guide and garden values selected bound to a comboboxes based on the activeguides and gardens tables respectively.

    Also, this is not something you should be doing in a query, except perhaps to test your relationships work as expected

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    To my eye: Gardens is a look up table - from the Activities perspective

    So In Activities table - change the Garden field to be a look up type field and the wizard will aid you in pointing to the Gardens table (this typically elicits comments from other developers on never using Look Up fields!! but they are fine in my opinion)

  4. #4
    toothpaste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9
    Quote Originally Posted by NTC View Post
    To my eye: Gardens is a look up table - from the Activities perspective

    So In Activities table - change the Garden field to be a look up type field and the wizard will aid you in pointing to the Gardens table (this typically elicits comments from other developers on never using Look Up fields!! but they are fine in my opinion)
    Actually Ajax was right, thank you both for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Lock a query for editing - run only
    By paul83 in forum Queries
    Replies: 3
    Last Post: 06-14-2016, 10:30 AM
  2. Editing query results, then appending to a table
    By Access_Novice in forum Queries
    Replies: 2
    Last Post: 03-28-2016, 08:55 PM
  3. Replies: 11
    Last Post: 03-13-2014, 09:54 AM
  4. Replies: 3
    Last Post: 10-19-2013, 10:21 AM
  5. prevent editing current entries in forms
    By Chazcoral in forum Forms
    Replies: 1
    Last Post: 05-20-2010, 06:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums