Solved

Expression "IIF" Statement based on existing tables

Posted on 2011-09-15
6
207 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
microsoft access - xml 10 29
Access Date Query 28 29
Access Run-time error '6' Overflow 5 13
DATETIMEOFFSET feature 1 0
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

746 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

13 Experts available now in Live!

Get 1:1 Help Now