Criteria for a query

[jaad]

I know that I should be able to wrap my head around this but for some reason my brain isn’t there today.
This is my query to which I need help in setting the fields criteria:

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 LEFT JOIN LocLivCondT ON LocSymbioseT.LocCondID = LocLivCondT.LocCondID;
 

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 won’t. 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 can’t put “10” in every field because I don’t return anything when I run the query.
From what I’ve learned so far I think a IIF statement would be require but don’t really know how to formulate it. The statement also must account for Null value as well. Can anyone help?

[orangeCat]

What is the question or query you're trying to answer?
In plain english, what are you hoping to find/select?

[jaad]

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.

[orangeCat]

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?

[jaad]

Because I need fields in LocSymbioseT for later use. maybe I can add it after the fact if it will help?

[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?

[jaad]

I've tried something similar to that but I can't because of the multivalues fields *IN.

[orangeCat]

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"?

[jaad]

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)

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 INNER JOIN LocLivCondT ON LocSymbioseT.LocCondID = LocLivCondT.LocCondID
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.

[jaad]

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

[orangeCat]

Glad to help, good luck with your project.