[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Expression "IIF" Statement based on existing tables

Posted on 2011-09-15
6
Medium Priority
?
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Industry Leaders: 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 …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

650 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