Microsoft Access - populate field from a table not in the query of a report

Worked well, Interestingly, I did not have to create a control on the report.
The DlookUp found the foreign table.

I'll still probably go with your reco to just add it to the query

Thank you!

If the field will have only a single value throughout the report, get it and put it in a hidden control on the form that runs the report.

=Forms!yourform!somecontrol

If the value changes based on data values in the report, the best solution as Nick alluded to is to simply add the table into your RecordSource query.

If that doesn't work for you, then use a DLookup() or DSum() or other domain aggregate but be prepared for the report to take longer.  And as Nick mentioned, any variables used in the domain function MUST be bound to controls in the report.  They may be hidden, but they must be bound.  The reason for this requirement is tat because Access thinks it is smarter than we are, it always rewrites your RecordSource queries for Reports (not for Forms though) and it specifically modifies the select clause to include ONLY fields that are bound to controls.  Apparently, the developer who thought this up never considered calculated fields or VBA for that matter.  We're stuck with it so once you've fought with the report for a day or two, everyone eventually binds the field and then promptly forgets how they solved the problem of the disappearing field.

The DlookUp found the foreign table.
DLookup has three arguments [expr], [domain] and [criteria]
[expr] is the desired field and mandatory
[domain] is the table/query and also mandatory
[criteria] is a valid SQL WHERE statement without 'where' and is optional
If you don't specify it though, you'll get a field value from the table from an indeterminate row.
In a small-enough table it's usually the first row.
With bigger tables that can break down and you'll get who-knows-what for a value.

Concurrent to what @PatHartman said
Forms will let you specify something like =Me.[SomeField] and will work
Reports do not, since the value of the field is all values in the query at once.
With the form, the value of the field depends on the current record.
Reports don't have that record-by-record change
Instead, you'll find that if you bind a control to a field with a changing value, and put it in a section that only gets rendered once (Like the ReportHeader) It will take the value of the field in the first record)
Similarly, if you put it in a section that renders a bunch of times (like a GroupHeader or Detail) it will take the value of the first record in play when that section renders.

So, when you reference a control on a report, that's something you have to keep very much in mind.

So something like
"orgn1 = 2"
is going to work just fine, since you've hardcoded it
Something like
"orgn1 = " & [SomeQueryFieldsValue] won't
while
"orgn1 = " & [SomeControlOnTheReport] will give you the value of the control at the time it renders.
Hope this helps

You guys rock, this sight rocks.  Total 'eye wide open' moment.
Got the queries knocked out.  The illumination on how lookups work in Reports
was great!

I hit two snags, both dlookup and adding to recordsource are not working.
In the data, there are blank spaces in the linking field.  I think this is causing the join in the recordsource to not work but not sure.  The same issue is impacting dlookup.  

I am contemplating trying an ADO Recordset call from within the pageheader region where I can use like and wildcard to overcome this issue.  Can I ask, is it possible to put an ADO Recordset sql where in the page header and have it return results to a text box?   It looks like the onpaint event is where I will place it.  Thanks for any follow up.  New learning for me.

Since you already accepted an answer that didn't work for you, you should either reopen the question or start a new one.

Did you try my suggestion to lookup the value prior to opening the report?

I'll keep pushing forward.  going to re-visit client on Monday and work on it.
I think you are right, I should start a new question.  The solution(s) you gave are spot on.
As is typical, that led to identifying a new problem.   Thanks again and have a great weekend!

In the data, there are blank spaces in the linking field.
Should there be?
It's definitely going to be a problem if there shouldn't be gaps

I am contemplating trying an ADO Recordset call from within the pageheader region where I can use like and wildcard to overcome this issue
ADO certainly works, but ultimately isn't necessary.
DLookup and DAO will handle wildcards and Like as well.

You haven't really posted a lot of detail about tables and data structure so it is hard to know what is going on.