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