How to write an expression for use on a report that adds the yes check boxes from 20 different UNRELATED tables?

I have 20 tables (Table1, Table 2,....) that all have a check box field called "Add to Report". The tables are unrelated. I tried may different expressions, but just get errors.
cssc1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can use DLookup:

=DLookup("CheckboxName","Table1")

or:

=Nz(DLookup("CheckboxName","Table1"),False)

or:

=DLookup("CheckboxName","Table1","[SomeFieldToCheck] = SomeValue")

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cssc1Author Commented:
Please see attached error messge.
I must be doing something wrong or I did a poor explination?
Error-Message.png
PatHartmanCommented:
If they are unrelated, why would they all go on the same report?

If you made the mistake of creating data silos and each of the tables has the same type of data but for a different month or different company or different "something", then you can bring them all back together in a single recordset by using a union query.  

Select tbl1.* From tbl1 Where tbl1.AddToReport = True
Union Select tbl2.* From tbl2 Where tbl2.AddToReport = True
Union Select tbl3.* From tbl3 Where tbl3.AddToReport = True
...
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gustav BrockCIOCommented:
You probably have to bracket the field name:

=DLookup("[Add to report]", ...

/gustav
cssc1Author Commented:
Adding the bracket worked, however, how do I write the expression to summ all the yes check boxes from all the other tables?
Gustav BrockCIOCommented:
That could be by adding similar expressions for the other tables:

=DLookup("[Add to report]", ...) + DLookup("[Add to report]", ...) + ... etc.

but if this what you need, I would prefer a method similar to that of Pat

However, if you really need this, you are in some kind of Deep trouble where you handle a database like it was a spreadsheet, which it is not. Thus, a redesign of your concept seems mandatory.

/gustav
PatHartmanCommented:
As Gus mentioned, the DLookup() method may work for you but it is extremely inefficient since each DLookup() requires a separate query and you would be doing 20 of them for each row of the main query.  The union query can be referenced in another query and the selected rows can be summed.  That would allow the query engine to optimize the process and will take less time to run.
cssc1Author Commented:
I tried to add the table up as noted in the sample expression, but another error.
I think I did it right???

Please see attached image.
cssc1Author Commented:
sorry, here is the image
Error-2.png
Gustav BrockCIOCommented:
You miss a double-quote in front of the table name.

/gustav
cssc1Author Commented:
Another error.
Did I do this right? I added the double quote and same problem.



=DLookUp(“[Add to Report],”"Root-Cause_1_tbl","[Human Engineering] = Yes") + DLookUp(“[Add to Report],”"Root-Cause_5_tbl","[Human Engineering] = Yes")
Gustav BrockCIOCommented:
You have to pay attention to the details. Now you have exchanged the first double-quotes with marks and repositioned the comma.

/gustav
cssc1Author Commented:
thanks
Gustav BrockCIOCommented:
You are welcome!

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.