[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

another query question

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
urjudo
Asked:
urjudo
  • 3
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
urjudoAuthor Commented:
all calendars are in one table
0
 
Rey Obrero (Capricorn1)Commented:
<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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
urjudoAuthor Commented:
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
 
PatHartmanCommented:
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
 
urjudoAuthor Commented:
Thanks!!! I did not see there is no space.  sorry
0
 
PatHartmanCommented:
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now