?
Solved

another query question

Posted on 2016-10-28
7
Medium Priority
?
71 Views
Last Modified: 2016-10-28
Hi Experts,
I have another query question.  I have a query below:
Cal/Dist: IIf([RMLOCATION] In ("Z (Dist 6)","H (Dist 6)","District 6x","District 6"),"District 6",IIf([RMLOCATION] In ("V (Dist 2)","District 2"),"District 2",IIf([RMLOCATION] In ("W (Dist 3)","G (Dist 3)","District 3"),"District 3",IIf([RMLOCATION] In ("X (Dist 4)","District 4"),"District 4",IIf([RMLOCATION] In ("Y (Dist 5)","District 5","Calendar87"),"District 5",IIf(Left([RMLOCATION],1)="C",[RMLOCATION]))))))

my question is how do I exclude the "calendar 87" on the IIf(Left([RMLOCATION],1)="C",[RMLOCATION]) since "Calendar 87 is included in the District 5 and I have to removed from the part of IIf(Left([RMLOCATION],1)="C",[RMLOCATION]).

Thanks,
0
Comment
Question by:urjudo
[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
  • 2
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41864318
it will be best if you create a table with all the combination that you want.
the query will then be very simple using a join to the combination table.
0
 

Author Comment

by:urjudo
ID: 41864339
all calendars are in one table
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41864349
<all calendars are in one table > what table? is there a relationship to this table and the other table in the query?

how about the other parts of your IIF() statements?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:urjudo
ID: 41864384
SELECT IIf([RMLOCATION] In ("Z (Dist 6)","H (Dist 6)","District 6x","District 6"),"District 6",IIf([RMLOCATION] In ("V (Dist 2)","District 2"),"District 2",IIf([RMLOCATION] In ("W (Dist 3)","G (Dist 3)","District 3"),"District 3",IIf([RMLOCATION] In ("X (Dist 4)","District 4"),"District 4",IIf([RMLOCATION] In ("Y (Dist 5)","District 5","Calendar87"),"District 5",IIf(Left([RMLOCATION],1)="C",[RMLOCATION])))))) AS [Cal/Dist]
FROM tblCourtRoom
GROUP BY IIf([RMLOCATION] In ("Z (Dist 6)","H (Dist 6)","District 6x","District 6"),"District 6",IIf([RMLOCATION] In ("V (Dist 2)","District 2"),"District 2",IIf([RMLOCATION] In ("W (Dist 3)","G (Dist 3)","District 3"),"District 3",IIf([RMLOCATION] In ("X (Dist 4)","District 4"),"District 4",IIf([RMLOCATION] In ("Y (Dist 5)","District 5","Calendar87"),"District 5",IIf(Left([RMLOCATION],1)="C",[RMLOCATION])))))), tblCourtRoom.STATUS
HAVING (((tblCourtRoom.STATUS)="Active"));
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41864390
how do I exclude the "calendar 87" on the IIf(Left([RMLOCATION],1)="C",[RMLOCATION])

The answer is - You Don't.  I believe in another post, I explained to you that the IIf() exits as soon as something falls into the final true path.

Your code isn't working because you have a typo.  There is no space between Calendar and 87 in the IIf()
0
 

Author Closing Comment

by:urjudo
ID: 41864398
Thanks!!! I did not see there is no space.  sorry
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41864411
PS - I agree with Rey.  This type of a conversion is better done with a cross-reference table.  I'm sure you are going to end up with more than one query that includes this logic and the table solves the entire problem in a consistent way.  You just change the table to move/add groupings.  The default is handled in the query.

Select ..., IIf(IsNull(tblGroup.GroupName), tblCourtRoom.RMLOCATION, GroupName) As Group
From tblCourtRoom Left Join tblGroup On tblCourtroom.RMLOCATION = tblGroup.RMLOCATION
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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