Solved

Expression "IIF" Statement based on existing tables

Posted on 2011-09-15
6
208 Views
Last Modified: 2012-05-12
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 :(
0
Comment
Question by:Shanan212
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:als315
ID: 36544529
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36544564
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36544750
<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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 13

Author Comment

by:Shanan212
ID: 36545019
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36545154
<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
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36545386
I've passed this stage and almost completed but I will consider this option next time!

Thanks indeed for letting me know!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now