Selection problem

[Mihail]

Hello !

I have 2 tables:
tblHouses (ID_House)
tblBulbs (ID_Bulb, ID_House, HasLight)
In tblBulbs the "ID_House" field is a look up on tblHouses and the "HasLight" field is of type Yes/No.

Now:
1) If each bulb in a house has light (HasLight=Yes), is OK.
2) If each bulb in a house hasn't light (HasLight=No), is, again, OK.
3) If some bulbs in a house have light and some bulbs in the same house haven't light, is NOT OK.

The question is:
How can I find the houses in situation 3) ?

I am looking for a smart solution, because I already have a solution "in force" :
In a query I have counted the bulbs from each house, in other query I have counted the bulbs with light for each house and, finaly, in the third query I do the comparation between the first and second query.

Thank you !

[ADezii]

I can get this to work with a Calculated Field calling a Public Function, but I'm sure the SQL Guys will have a better approach. I'll post what I have for reference anyway.

1. tblBulbs:
   

   ID_House     ID_Bulb       HasLight
   House 1          1           Yes
   House 2          1           No
   House 3          1           Yes
   House 3          2           No
   House 3          3           Yes
   House 4          1           Yes
   House 4          2           Yes
   House 4          3           Yes
   House 4          4           Yes
   House 4          5           Yes
   House 4          6           Yes
    

2. SQL:
   

   SELECT DISTINCT tblBulbs.ID_House, fCalcNotOK([ID_House]) AS OK_Or_NOT
   FROM tblBulbs;
    

3. Function Definition:
   

   Public Function fCalcNotOK(strHouseID As String) As String
   If DCount("*", "tblBulbs", "[ID_House] = '" & strHouseID & "' and [HasLight]=False") > 0 And _
      DCount("*", "tblBulbs", "[ID_House] = '" & strHouseID & "' and [HasLight]=True") Then
        fCalcNotOK = "Not OK"
   Else
        fCalcNotOK = "OK"
   End If
   End Function

4. Results:
   

   ID_House    OK_Or_NOT
   House 1        OK
   House 2        OK
   House 3        Not OK
   House 4        OK
    

[Mihail]

Thank you, ADezii !
Good enough for me but I don't wish to close (yet) this thread by selecting your answer as the best answer.
That because I wonder too what solution can find the "SQL Guys".

[ADezii]

I would wait also, since I do believe that there is a purely more efficient SQL approach. I'll attempt one later but SQL is definitely not my 'Cup-of-Tea' (LOL).

[Rabbit]

I would have done similar to @Mihail's 3 query approach. I don't know if this would be more efficient but you could also do something along the lines of this:

SELECT
   ID_House,
   IIf( 
      COUNT(*) = SUM(IIf(HasLight = True, 1, 0))
         OR
      COUNT(*) = SUM(IIf(HasLight = False, 1, 0))
   , 'OK', 'Not OK') AS OK_Or_Not
 
FROM tblBulbs
 
GROUP BY ID_House

[Mihail]

Thank you, guys !
Seems that are not other solutions.

I have implemented ADezii's solution (in fact, idea) because I understand better [even if he has multiple houses for one bulb :) . Energy economy.].

My skill in SQL is almost null. So I must say thank you to Rabbit, but I'm afraid that I'll not be able to manage the SQL if I'll have some changes in code in the future.

I have select the best answer only from this view point, because really I don't know which one is better.

Thank you again !