Improve company productivity with a Business Account.Sign Up

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

query question

Hi Experts,
I have another query question, this one is a little complicated.  I have two field in a query from two different table, whenever I run the query, it's always pull the data for the first IIF.

field 1:   TEAM: IIf([CPTeam] In ("00","PD"),"DC",IIf([CPTeam]="IS","IS",IIf([CPTeam]="SU","SU")))
field 2:   Cal: IIf([Child]![Calendar] In ("Calendar R","Calendar P","Calendar T"),"DC/SD",IIf([Child]![Calendar] In ("Calendar R","Calendar
                T"),"IS/BD"))

I tried tem = "IS"  and Cal = "IS/BD", the result I got was  empty, I know I have two records,   have not idea why

Thanks
0
urjudo
Asked:
urjudo
  • 6
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
what exactly do you want to see? are the two tables related?
0
 
urjudoAuthor Commented:
Yes, these two table link with case#, I want to put the records under these criteria, but it seems not correct.
0
 
urjudoAuthor Commented:
Team DC can be has calendar R or Calendar T so does Team IS can be has Calendar R or Calendar T
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
this expression

field 2:   Cal: IIf([Child]![Calendar] In ("Calendar R","Calendar P","Calendar T"),"DC/SD",IIf([Child]![Calendar] In ("Calendar R","Calendar
                T"),"IS/BD"))

will always return "DC/SD"

try this instead

Cal: IIf([Child]![Calendar] In ("Calendar R","Calendar T"),"IS/BD",IIf([Child]![Calendar] In ("Calendar R","Calendar P","Calendar T"),"DC/SD"))
0
 
urjudoAuthor Commented:
your way is working but can you explain to me why the one I did always return to DC/SD?  coz I will have two more of the DC/KM and DC/SC.
0
 
urjudoAuthor Commented:
now I encounter with another problem, when I add calendar S under the DC/SD, one of the record shows IS/BD under the Cal (this record is Calendar R and it should be DC/SD) , do I have to add the team under the Cal too?
0
 
Rey Obrero (Capricorn1)Commented:
this expression

field 2:   Cal: IIf([Child]![Calendar] In ("Calendar R","Calendar P","Calendar T"),"DC/SD",IIf([Child]![Calendar] In ("Calendar R","Calendar
                T"),"IS/BD"))

will always return "DC/SD"

since the second part  ("Calendar R","Calendar T")," is already a part of the first condition  ("Calendar R","Calendar P","Calendar T"),"
0
 
Rey Obrero (Capricorn1)Commented:
better upload a copy of the db
0
 
PatHartmanCommented:
The IIf() exits as soon as it finds a "true" solution.  Your IIf() as written doesn't make any sense.

 Cal: IIf([Child]![Calendar] In ("Calendar R","Calendar P","Calendar T"),"DC/SD",IIf([Child]![Calendar] In ("Calendar R","Calendar
                T"),"IS/BD"))

How can "Calendar R" result in "DC/SD" AND "IS/BD"?
0
 
urjudoAuthor Commented:
That's why I have the team as field 1  in the query too.  I tried to figure it out if this can be done.
0
 
PatHartmanCommented:
If you are saying that Cal depends on Team, you need to repeat the Team condition in the Cal expression.

Try this.  It is just a guess but it shows two conditions, both of which must be true.  Your conditions may be more complex.  I can't tell from what you posted what would be correct.  In the IIf() below, if Team is in ("00","PD") AND calendar is in ("Calendar R","Calendar P","Calendar T"), the result is DC/SD, otherwise the result is IS/BD.

Cal:IIIf([CPTeam] In ("00","PD"),IIf([Child]![Calendar] In ("Calendar R","Calendar P","Calendar T"),"DC/SD","IS/BD"))
0
 
urjudoAuthor Commented:
I finally have the query like this:

Cal: IIf([Child]![Calendar] In ("Calendar 98","Calendar K","Calendar Q","Calendar 94","Calendar M","Calendar 95","Calendar R","Calendar T","Calendar 97","Calendar 89","Calendar 99","Calendar S","Calendar U") And IIf([CPTEAM]="IS","IS"),"IS",IIf([Child]![Calendar] In ("Calendar S","Calendar U","Calendar 89","Calendar 99") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/SD",IIf([Child]![Calendar] In ("Calendar R","Calendar T","Calendar 95","Calendar 97") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/SC",IIf([Child]![Calendar] In ("Calendar M","Calendar K","Calendar Q","Calendar 98","Calendar 94") And IIf([CPTEAM] In ("00","PD"),"DC"),"DC/KM",IIf([Child]![Calendar] In ("Z (Dist 6)","H (Dist 6)","District 6x","Calendar 87","District 6","V (Dist 2)","District 2","W (Dist 3)","G (Dist 3)","District 3","X (Dist 4)","District 4","Y (Dist 5)","District 5") And IIf([CPTEAM]="SU","SU"),"SU")))))

this works what I need, but anyone as better way to cut the query shorter and have the same result?  coz I try to lean to do in a better way.

Thanks,
0
 
PatHartmanCommented:
I would probably make a table.  That makes the conversion easy to understand and easy to modify.  Join using a left join to the conversion table and return "error" if nothing is found.  That way if new values are added and the conversion table isn't updated, you won't loose records, you'll just see an error indication.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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