Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

query question

Posted on 2016-10-26
13
Medium Priority
?
74 Views
Last Modified: 2016-11-01
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
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
  • 6
  • 4
  • 3
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41861111
what exactly do you want to see? are the two tables related?
0
 

Author Comment

by:urjudo
ID: 41861122
Yes, these two table link with case#, I want to put the records under these criteria, but it seems not correct.
0
 

Author Comment

by:urjudo
ID: 41861126
Team DC can be has calendar R or Calendar T so does Team IS can be has Calendar R or Calendar T
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.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 41861135
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
 

Author Comment

by:urjudo
ID: 41861154
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
 

Author Comment

by:urjudo
ID: 41861180
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41861186
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41861188
better upload a copy of the db
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41861214
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
 

Author Comment

by:urjudo
ID: 41861224
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 41861269
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
 

Author Comment

by:urjudo
ID: 41862892
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 41863079
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

670 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