stuck on an inner join query with distint and union

[markjkubicki]

i am certain that i have been able to do this prior, so i apologize in advance, but this time (haven 'taken a break from it' SEVERAL TIME) I am still stuck



in its simplest, i have a single form, with a continuous subform, having only a combobox

the combo box displays notes that have been selected, from 'some other table', for the current record, and allows data entry of additional notes (from it's drop down.) the record source ought to list all records in the table Tag_NotesRefs (list of all notes used for all records in the table Tags) that have not been used in record current at form1

it also ought to include the (union) the sole record in table Notes_addl, which will offer a prompt to get notes (through a pop-up form) from a 'some other table' in a linked MDb.

I have spun around on this so many times, I'm not even certain how to ask, and I KNOW that i have not explained this well at all, but i think (I hope) you'll get the jest from the attached...

ANY help will be greatly appreciated in advance,
markSTUCK_2022.06.07.zip

[June7]

Neither form RecordSource nor combobox RowSource reference "[some other table]" so why do you say "the combo box displays notes that have been selected, from 'some other table', for the current record"? Nothing is displaying data from "[some other table]".

"the record source ought to list all records" - did you mean "row source"?

Relationships are not clear to me. What is destination table and what is lookup table?

Possibly you need something like:
SELECT field1, field2, field3 FROM sometable WHERE ID NOT IN (SELECT ID_FK FROM othertable WHERE somefield = somevalue)
UNION SELECT 0, 999, "< add additional notes from the MDb >" FROM sometable
ORDER BY field1;

I don't get the gist nor do I think you jest.