Solved

query question

Posted on 2016-10-26
13
54 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 35

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 35

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 35

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

813 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

12 Experts available now in Live!

Get 1:1 Help Now