Solved

query question

Posted on 2016-10-26
13
62 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 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
 
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 36

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 36

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 36

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

830 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