By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Criteria for a query

100+
P: 158
I know that I should be able to wrap my head around this but for some reason my brain isnt there today.
This is my query to which I need help in setting the fields criteria:
Expand|Select|Wrap|Line Numbers
  1. SELECT LocSymbioseT.LocCondID, LocSymbioseT.LocID, LocSymbioseT.TenantID, LocSymbioseT.BuildingID, LocLivCondT.LWallTrimIN, LocLivCondT.LCeilingIN, LocLivCondT.LfloorIN, LocLivCondT.LTvTelephoneSwitchOutletIN, LocLivCondT.LClosetIN, LocLivCondT.LIlluminationIN, LocLivCondT.LWindowCoveringIN, LocLivCondT.LThermostatIN
  2. FROM LocSymbioseT LEFT JOIN LocLivCondT ON LocSymbioseT.LocCondID = LocLivCondT.LocCondID;
  3.  
LocCondID = Parent/Child
LocID=The suite
TenantID= The Tenant who occupies the suite
BuildingID= The building in which the suite is located
*IN=The fields I need to set criteria for
Every field can have one of eleven possible ID Reference number of a letter code. The code that I am after is R or 10(as I use its ID instead which stands for repair)
So each *IN field can potentially have a R code value but unlikely as everything in the suite would trashed and everything would require repair. So some will have a R some wont. What I need is a criteria that will extract the R from any and all of them.
The *IN fields are all multi-select because there is the possibility to have a combination of letter codes for each field.
If I put a 10 in the criteria box and if there is a record that has an R in the field it will show up. The problem is I cant put 10 in every field because I dont return anything when I run the query.
From what Ive learned so far I think a IIF statement would be require but dont really know how to formulate it. The statement also must account for Null value as well. Can anyone help?
Feb 12 '10 #1

✓ answered by orangeCat

My question really is why a Left Join as compared to EQuiJoin?
For example:



SELECT
LocSymbioseT.LocCondID
, LocSymbioseT.LocID
, LocSymbioseT.TenantID
, LocSymbioseT.BuildingID
, LocLivCondT.LWallTrimIN
, LocLivCondT.LCeilingIN
, LocLivCondT.LfloorIN
, LocLivCondT.LTvTelephoneSwitchOutletIN
, LocLivCondT.LClosetIN
, LocLivCondT.LIlluminationIN
, LocLivCondT.LWindowCoveringIN
, LocLivCondT.LThermostatIN
FROM LocSymbioseT , LocLivCondT
WHERE
LocSymbioseT.LocCondID = LocLivCondT.LocCondID AND
(LocLivCondT.LWallTrimIN = "R" or
LocLivCondT.LCeilingIN = "R" or
LocLivCondT.LfloorIN = "R" or
LocLivCondT.LTvTelephoneSwitchOutletIN = "R" or
LocLivCondT.LClosetIN = "R" or
LocLivCondT.LIlluminationIN = "R" or
LocLivCondT.LWindowCoveringIN = "R" or
LocLivCondT.LThermostatIN = "R");

Does this query give you what you need?

Share this Question
Share on Google+
10 Replies


P: 83
What is the question or query you're trying to answer?
In plain english, what are you hoping to find/select?
Feb 12 '10 #2

100+
P: 158
The database is about maintaining suites in an apartment building. This example that I gave is only one table. I have several for different area in the suite. Example: Living room table, kitchen, table , bathroom table and so on. when I perform a condition report each item in the suite is given a grade: Good, Fair, Poor, Stained, Broken, Repair ect.

what I want to have is when I finished entering the data for a condition report, all item that requires "repair" or "R" to be pulled out so I can create a work order about the item.
Feb 12 '10 #3

P: 83
Ok, another question. Why do you use Left Join?
Any o fthe fields *IN could have a "R" epair which you want to select -- right?
Feb 12 '10 #4

100+
P: 158
Because I need fields in LocSymbioseT for later use. maybe I can add it after the fact if it will help?
Feb 12 '10 #5

P: 83
My question really is why a Left Join as compared to EQuiJoin?
For example:



SELECT
LocSymbioseT.LocCondID
, LocSymbioseT.LocID
, LocSymbioseT.TenantID
, LocSymbioseT.BuildingID
, LocLivCondT.LWallTrimIN
, LocLivCondT.LCeilingIN
, LocLivCondT.LfloorIN
, LocLivCondT.LTvTelephoneSwitchOutletIN
, LocLivCondT.LClosetIN
, LocLivCondT.LIlluminationIN
, LocLivCondT.LWindowCoveringIN
, LocLivCondT.LThermostatIN
FROM LocSymbioseT , LocLivCondT
WHERE
LocSymbioseT.LocCondID = LocLivCondT.LocCondID AND
(LocLivCondT.LWallTrimIN = "R" or
LocLivCondT.LCeilingIN = "R" or
LocLivCondT.LfloorIN = "R" or
LocLivCondT.LTvTelephoneSwitchOutletIN = "R" or
LocLivCondT.LClosetIN = "R" or
LocLivCondT.LIlluminationIN = "R" or
LocLivCondT.LWindowCoveringIN = "R" or
LocLivCondT.LThermostatIN = "R");

Does this query give you what you need?
Feb 12 '10 #6

100+
P: 158
I've tried something similar to that but I can't because of the multivalues fields *IN.
Feb 12 '10 #7

P: 83
I don't understand. Can you show a couple of data records?

Are you not looking for records where any or any number of these fields whose name ends in "IN" contain an "R"?
Feb 12 '10 #8

100+
P: 158
Here is another representation. "One that doesn't work unless all field have a value "10" (10 is the ID for "R" in those multivalues fields)

Expand|Select|Wrap|Line Numbers
  1. SELECT LocSymbioseT.LocCondID, LocSymbioseT.LocID, LocSymbioseT.TenantID, LocSymbioseT.BuildingID, LocLivCondT.LWallTrimIN, LocLivCondT.LCeilingIN, LocLivCondT.LfloorIN, LocLivCondT.LTvTelephoneSwitchOutletIN, LocLivCondT.LClosetIN, LocLivCondT.LIlluminationIN, LocLivCondT.LWindowCoveringIN, LocLivCondT.LThermostatIN
  2. FROM LocSymbioseT INNER JOIN LocLivCondT ON LocSymbioseT.LocCondID = LocLivCondT.LocCondID
  3. WHERE (((LocLivCondT.LWallTrimIN.Value)=10) AND ((LocLivCondT.LCeilingIN.Value)=10) AND ((LocLivCondT.LfloorIN.Value)=10) AND ((LocLivCondT.LTvTelephoneSwitchOutletIN.Value)=10) AND ((LocLivCondT.LClosetIN.Value)=10) AND ((LocLivCondT.LIlluminationIN.Value)=10) AND ((LocLivCondT.LWindowCoveringIN.Value)=10) AND ((LocLivCondT.LThermostatIN.Value)=10));
Which would probably never happen. In a real situation, one or two , even maybe three field would have a "10" in it. so no mater if the other field have it or not I still want the ones that do have a value of 10 to come out on the query.
Feb 12 '10 #9

100+
P: 158
yIPEE ok I got it. I did what you suggested. but I did it the right way. I was entering them all and using AND instead of OR. I knew my brain wasn't working right today. thanks a bunch Orange Cat
Feb 12 '10 #10

P: 83
Glad to help, good luck with your project.
Feb 12 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.