Expression "IIF" Statement based on existing tables

Hi,

Thank you for offering to help!

LTL: [getB]![LTL] + [getT]![MIN] + [getC]![MIN] 

Open in new window


The above expression is in my query design.

I want to know if I can make it only add the tables that exit. This is because, all the above tables are made through other queries. Some queries run under some conditions, etc.

So I want to know that using an 'iif' statement I can check if a table exists then add them? (only add fields from tables that exists)

I want to know if its possible if then how?

- The solution I have in mind is far complicated. That is, call a function for each table within expression builder

Eg: iff (FUNCTION1() = "true"; iif(function2() = "true", iff (function3() ="true",[getB]![LTL] + [getT]![MIN] + [getC]![MIN]), [getB]![LTL]), [getB]![LTL] + [getT]![MIN]),[getT]![MIN] + [getC]![MIN] )

...gah this is complicated...I dont think above a possible code :(
LVL 13
Shanan212Asked:
Who is Participating?
 
Nick67Connect With a Mentor Commented:
<I do not know. how to do a nested query (eg: using a result from another query on other query) but learning it/troubleshooting it would be another whole level (again time limit) >

That is very simple.
Lets say I have "Select * from SomeTable Where SomeCondition = True;"
I save it as 'qrySomeCondtionTrue'

I can then do "Select * from qrySomeCondtionTrue where SomeThingElse = SomeValue;"

It's that simple
0
 
als315Commented:
May be you can add details to your task:
"all the above tables are made through other queries" - I like more add queries. In this case you can always use them.
You can also construct query in VBA. In this case you can select tables you like to see.
0
 
Shanan212Author Commented:
Aah, making a query in VBA - that I know. But I am afraid its going to be very complicated heavy query which is why I skipped the notion.

The tables that are being added themselves are made through filter-queries which are based on IF statements in my VBA.

So to make a query that will add all/some then I have to modify the query themselves

What I have now is, I want to see if I can do this via expression builder/VBA without writing SQL.

Worst case, I will have to create a query for each add in query design; which I already finished. But I am checking if this is possible..if then I can cut down on the #queries being run and tables hence the file size.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Nick67Commented:
<This is because, all the above tables are made through other queries>
I have seen some of your other questions.
You appear to be doing a LOT of make-table queries and then deleting tables.
Over time, this will cause you immense grief in MS Access.

The structure of a table should be something that persists, it is a place to store data permanently.
Select queries are virtual tables.  You can use them as the basis of other queries if you need to.
There is no need to save the results of a select query as a table before using the data in most cases.

Would you care to post your database for folks to evaluate what you are doing?
0
 
Shanan212Author Commented:
I could post it but its 175mb due to some tables. What I am doing is creating an engine for over 1Bill possible data combinations

I previously did a 'data-based' access database where a user can "filter" what they desire (results - so results are already built in) and it went over 2GB while hitting 200M records.

So I am doing an engine which can generate this. Except at maximum capacity, it will out put 11M records to a table.

It does have large tables using which it pulls it data.

Now what you are saying is correct. But I have minimum options as I am on a tight deadline and I do not know. how to do a nested query (eg: using a result from another query on other query) but learning it/troubleshooting it would be another whole level (again time limit)
0
 
Shanan212Author Commented:
I've passed this stage and almost completed but I will consider this option next time!

Thanks indeed for letting me know!
0
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.

All Courses

From novice to tech pro — start learning today.