Results 1 to 3 of 3
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    "ambiguos outer join" ... I think

    once yet again, i find myself in the land of the ambiguous outer join (I think)


    and although in the past I've persevered, and figured it out... today, I'd really just like to ask: "what does it want to be?" (with no malice reference to Androgynous Pat - SNL intended)

    table tblThings has a field txtTag
    it is related to table tblTagNotes thru that field
    (tblThings will have many records, each of with may have many notes; each of these notes potentially will be common other records)

    additionally there is a table tblNoteText (which stores the actual text for the note ...that is being referenced by (many records) in tblTagNotes) It has (3) fields: sglNoteID, txtNoteTitle, and txtNoteText

    tblTagNotes is a middle man (common to both tblThings and tblNoteText). It has (2) fields: txtTAG (common with tblThings) and sglNoteID (common with tblNoteText)

    each record in tblNoteText is related to tblTag_Notes thru the field sglNoteID


    to recap:there are many records in tblThings, and each may reference many notes that are common to many other records in that table
    so it is a one-to-many-to-one relationship (?)


    the (simple) CHALLENGE IS:
    - on a data entry form, I have a data entry sub-form sfrmTagNotes
    - it should display the field tblNoteText.txtNoteTitle as the row-source of a combo-box
    - but store the field sglNoteID in the related table tblTagNotes


    ? what type of relationships should i have
    ? should the relationships be on the front end, or the back end tables
    ? what else should I be looking out for that is going to catch me by surprise


    sorry for being lazy (but i do offer INFINITE thanks in advance),
    mark

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of the database (if any private stuff - remove before posting)?

    Usually an ambiguous join error message will show up when you attempt to run a query. Not sure why you say "I think".

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    so it is a one-to-many-to-one relationship (?)
    Impossible to say without knowing if it's one unique thing to many notes (thus is one to many) or if Thing isn't unique, in which case it would be Many to Many. You don't really say. As noted, a copy of the db with sample data, or very precise explanation of the records in each table is required. The former should eliminate much of the questions we might have. It also reads like your combo should have at least 2 fields with 1 being hidden so that choosing a value from a visible list causes the value from the bound and invisible column to be written to the table.
    Last edited by Micron; 06-27-2018 at 08:59 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 6
    Last Post: 10-19-2016, 03:40 AM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 3
    Last Post: 05-15-2013, 02:49 PM
  4. "Join expression not supported"?
    By jsoldi in forum Queries
    Replies: 1
    Last Post: 08-29-2011, 01:21 PM
  5. Problem with "join" duplicating records
    By Zukster in forum Queries
    Replies: 0
    Last Post: 08-25-2009, 09:00 AM

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