Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't edit field (2002 SP3)

    I have 2 tables joined in a query, with a one to one left outer join. Right table = tblA, Left table = tblB. They are joined on ID. The ID field in each table is the Primary key and both are set to no duplicates. In the query, when I try to add a value to a field in the right table that has no record, Access won
    Regards,
    Peter

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

    Re: Can't edit field (2002 SP3)

    I'm confused: why is ID the primary key in BOTH tables? That means you have a one-to-one relationship between the tables. Unless you have a very special reason, one-to-one relationships are not a good idea, they are cumbersome to work with.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't edit field (2002 SP3)

    Why is that? I thought that if I got the situation where I could put the fields in the main table (tblA) but only a few records of that table would have the fields filled, that the best alternative was to create a separate table.
    In this case that is in fact the relationship. Not every tblA record will have a corresponding tblB record., but where there is one, that will be the only record.
    Regards,
    Peter

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

    Re: Can't edit field (2002 SP3)

    I always find 1-to-1 relationships a PITA to work with, but there are some situations where you need them.

    But now I'm confused by your description: if tblA is the main table, it should be the "left" table in the left join, not the "right" table as you stated in the first post in this thread.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't edit field (2002 SP3)

    Duh! Glad one of us is awake.
    tblA = left table; tblB = left table
    Regards,
    Peter

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

    Re: Can't edit field (2002 SP3)

    > tblA = left table; tblB = left table

    Still not quite awake? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't edit field (2002 SP3)

    an embarrassed Double Duh!
    tblA = left table; tblB = right table
    Regards,
    Peter

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

    Re: Can't edit field (2002 SP3)

    You should be able to enter or edit data in both tables. The ID field on one side should automatically be duplicated on the other side.

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't edit field (2002 SP3)

    That's what I thought.
    I've come across this several times before, in different Dbs. I have always written code to insert the ID in a new record, I can then enter data. But this seems an awkward way of overcoming a problem that shouldn
    Regards,
    Peter

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

    Re: Can't edit field (2002 SP3)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  11. #11
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't edit field (2002 SP3)

    There are 2 other tables in the query, when I remove them I can edit the fields. However I need those tables as criteria. Any suggestions as to how to make the comments field in Query1 editable?
    Regards,
    Peter

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

    Re: Can't edit field (2002 SP3)

    Try this:

    SELECT Bayrole.ID, Bayrole.Surname, tblBCKidz.ID, tblBCKidz.Comments
    FROM Bayrole LEFT JOIN tblBCKidz ON Bayrole.ID = tblBCKidz.ID
    WHERE Bayrole.ID In (SELECT PersID FROM tblChCatLink INNER JOIN tblChurchCats ON tblChCatLink.CatID = tblChurchCats.CatID WHERE CatName = "BCKidz")
    ORDER BY Bayrole.ID;

  13. #13
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't edit field (2002 SP3)

    Once again, thanks so much Hans. Works like a charm. The genius strikes again!
    Regards,
    Peter

Posting Permissions

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