Solved

query question

Posted on 2016-10-26
13
41 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
  • 6
  • 4
  • 3
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 41861188
better upload a copy of the db
0
 
LVL 34

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 34

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 34

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now