470,822 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,822 developers. It's quick & easy.

IIF (term exists in a table, "true","False")

I have a table that I want to analyze for multiple data points (true/false or 'exists in another table). Because I want to analyze more than one point, doing a join a showing Is Null won't work. At least, I can't get it to work.

What I'm looking for is a query function to produce a series of columns, each checking different criteria.

Example Table:
Intro ACS ACS-1000 001 MAIN 1D04 9/7/11 12/20/11
Adv. ACS ACS-2000 080 MAIN 2D17 9/8/11 12/20/11
Special ACS ACS-2150 750 ONLINE 9/7/11 11/30/11

I want to be able to add a series of columns that checks each entry for things like:
• Is Start date standard? (checking against another table of standard start times)
• Is End date standard? (same as with start)
• Location "MAIN" must have a room number
• Start and End Time (not shown) must match another table of standard start and end times.

I want my end report to produce only courses with errors, and to identify which errors are present for each course.

my thinking is that I could do a series of query columns like:
StartCheck: IIF([Start]=[tbl].[StartTimes],"OK","Start Time Error")

but I don't know how to use IIF to check if a term exists in another table.
Oct 27 '11 #1
5 7678
32,311 Expert Mod 16PB
Why not look at referential integrity and design your database such that data can only be entered if the values are valid? It sounds a whole lot easier an approach to me.
Oct 27 '11 #2
32,311 Expert Mod 16PB
Otherwise, you would typically design a query that included various LEFT JOINS (in the SQL) and the value of the check field would reflect whether or not the field value is Null. Checking for Null in a query (SQL) is done by saying ([X] Is Null) and can be used within IIf() calls as well as elsewhere.
Oct 27 '11 #3
I should have mentioned: the data is being extracted from another (rather limited, closed, proprietary) program that, for various reasons, allows users to enter data that doesn't conform to 'normal.' So I'm not able to provide entry-point validation. It's my job to identify these anomalies and figure out if they're user error or genuinely acceptable exceptions (which is quite common). I'm importing the data via Linked Tables, so I have no control over what data is allowed.

I should also point out that I'm extremely new to SQL, but I'm a quick learner, as long as you show me an example of how to write things :)

When doing LEFT JOINS, is it possible to do a series of them in the same query? ie: I want to compare two different columns in my imported table (START and END) to a column in my Dates table... Or am I answering my own question that Start and End should be checked against different columns?
Oct 27 '11 #4
I think I may have an answer. I needed to do a select nested in an IIF... (Qcv is a query producing only non-canceled courses for analysis):

Expand|Select|Wrap|Line Numbers
  1. SELECT Qcv.Title, Qcv.Course, Qcv.Sec, Qcv.Location, Qcv.Room, Qcv.Day, Qcv.Start, Qcv.End, Qcv.Start1, Qcv.End1, 
  2. IIf([Start1] In (select StandardDate from Dates),"","Check Start Date") AS StartDateCheck, 
  3. IIf([End1] In (select StandardDate from Dates),"","Check End Date") AS EndDateCheck, 
  4. IIf([Start] In (select SlotStart from Slots),"","Check Slot Start Time") AS SlotStartCheck, 
  5. IIf([End] In (select SlotEnd from Slots),"","Check Slot End Time") AS SlotEndCheck, 
  6. IIf([Location]="MAIN",IIF([Room] Is Null,"Location MAIN with no room",""),"") AS LocationCheck
  7. FROM Qcv;
Now to produce a filter to exclude entries not meeting any of these criteria and I'll have a clean report with only 'non-standard' entries...
Oct 27 '11 #5
32,311 Expert Mod 16PB
Joel Marion:
When doing LEFT JOINS, is it possible to do a series of them in the same query?
Yes. As you've asked to be pointed in the right direction I'd suggest you use the Query Designer to create a query with multiple tables/queries as input. From there you would link the fields in Qcv to the relevant fields in the other tables/queries. Double-click on each join line to specify a LEFT JOIN and then you have the basics illustrated. To see what has been created, and to see how multiple such links are handled, view the query in SQL mode and you'll see the actual SQL created by Access. By looking at that I'm sure you'll appreciate the concept (It may be advisable to start with only a couple of links to keep it simple).

While your suggested alternative would produce adequate results eventually, I would expect it to perform excessively slowly relative to a properly designed query.
Oct 27 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

36 posts views Thread by toedipper | last post: by
7 posts views Thread by kittykat | last post: by
9 posts views Thread by Ed_No_Spam_Please_Weber | last post: by
1 post views Thread by Colin Spalding | last post: by
1 post views Thread by Kevin | last post: by
117 posts views Thread by phil-news-nospam | last post: by
12 posts views Thread by sandiptaylor | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.