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?
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
0

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
0
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
...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

/gustav
0
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?
0
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
0
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.
0
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.
0
cssc1Author Commented:
sorry, here is the image
Error-2.png
0
Gustav BrockCIOCommented:
You miss a double-quote in front of the table name.

/gustav
0
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")
0
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
0
cssc1Author Commented:
thanks
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.