?
Solved

Expression "IIF" Statement based on existing tables

Posted on 2011-09-15
6
Medium Priority
?
229 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 40

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

809 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