IN in query

[Robert Painter]

Hi,

I have the following query:

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCategory As String
Dim strSkill As String
Dim strOption As Integer
Dim mySql As String
' getting choices from list box (note list categories not used)
For Each varItem In Me.LstSkill.ItemsSelected
strSkill = strSkill & "," & Me.LstSkill.ItemData(varItem)
Next varItem
' checking length of resulting string
If Len(strSkill) = 0 Then
strSkill = "Like '*'"
Else
strSkill = Right(strSkill, Len(strSkill) - 1)
strSkill = "IN(" & strSkill & ")"
' getting option group into stroption
strOption = Me.grpStatus
End If
Set db = CurrentDb
mySql = "SELECT tblEmployees.EmployeeName " & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills.SkillIDFK " & strSkill & "" & _
" And tblEmployees.EmploymentStatus = " & strOption & "" & _
" ORDER BY tblEmployees.EmployeeName"
MsgBox mySql
Set qdf = db.QueryDefs("qryskillsListQuery")
qdf.SQL = mySql
DoCmd.OpenQuery "qrySkillsListQuery", acViewPreview
DoCmd.Close acForm, "frmSkillListQuery"
Set qdf = Nothing
Set db = Nothing

End Sub

The idea being to get WHERE data from multilist table: strSkill which may be
4 or 5 items.
I have just realised IN acts as OR and i want it to act as AND

any help please

Robert

 

[Robert Painter]

I think i have solved this problem with NOT IN.

Would like confirmation if I am correct though.

Robert

 

[Michel Walsh]

An IN list is an OR, indeed. A SINGLE value cannot be equal to two different
values:

x = 4 AND x = 5


is always false (or null), since if x=4, it cannot ALSO (AND) be equal to
5.


So, an IN list acts like an OR.

x IN(4, 5) is the same as x=4 OR x=5


From the Morgan's law of negation, a NOT IN becomes a list of AND:

NOT x IN(4, 5) is the same as x <> 4 AND x <> 5




If that is what you want, I assume those simple constructions are just
right. On the other hand, if you want something like: get the employee
which have ALL (AND) the following qualifications, you cannot do it with a
simple IN since you have to involve many records. If *this* is what you
seek, take a look at a possible solution for this more complex problem at
http://www.mvps.org/access/queries/qry0016.htm




Vanderghast, Access MVP

 

[Robert Painter]

Hi Michel,
In the code given i am trying to find from my tblemployees (in trial only 10
entered) all the desired skills (tblSkills) selected thru multi-select list
box. so i am looking for all employees with all of the following skills:
typing, switchboard, secretary, bookkeeping. which is why i was getting
incorrect results from the IN predicate. If IN is an OR then is NOT IN an
AND. If this is so then that surely will give the correct result of: Find
all employees having skills typing AND switchboard AND secretary AND
bookkeeping.

I read the link you sent and although i can see what he is aiming to achieve
he is not giving a difinitive statement of what to use to achieve the
outcome I require. So the question is still the same: Is NOT IN work the
same as AND.

Robert

 

[Michel Walsh]

The answer to this question is that IN (alone) is not appropriate in that
case.


In the case implying a single table, no join involved, any WHERE clause is
applicable to data in ONE record of the table at a time.

Assuming you use a normalized table:



Mary typing
Mary switchboard
Mary secretary
Mary first aid technique
Mary book keeping
John typing



you can then use:

SELECT *
FROM myTable
WHERE skills IN("typing", "switchboard", "secretary", "book keeping")



will return the four relevant records of Mary, and the single record for
John. But that is still not what you want. You cannot progress any further
with the WHERE clause. On the other hand, "four" records for Mary and "one"
record for John lead us to the solution: COUNT the number of records by
user:


SELECT user
FROM myTable
WHERE skills IN("typing", "switchboard", "secretary", "book keeping")
GROUP BY user
HAVING COUNT(*) = 4



which now, returns only Mary, since John's count of number of records is of
one, not four.



The previously given reference simply illustrates a more generic case, where
the IN-clause is variable, and also, deal with our magical value of 4, here
used as criteria on the COUNT.




So, to repeat myself, remember that without join, the WHERE clause can ONLY
deal with data in ONE record at a time. So, the IN clause itself is unable
to solve, alone, the problem requiring to consider multiple records.



Vanderghast, Access MVP

 

[Robert Painter]

Michel.
Thanks for the brief tutorial. I now understand your reasoning and have to
agree. Two questions:

How do you get the count number ?? programaticall as in my code given. and
second question

Why is access so frustratingly difficult. I have been working on this
project for weeks and learn something new every time i think it is nearly
finished thus giving me more and more work to do.

Still keeps the grey matter active.

Thanks for you help. sometimes it is like switching a light on.

Regards

Robert

 

[Michel Walsh]

How to get the count number? If the desired skills are in a table, you can
use DCount("*", "Skills"), or the equivalent sub-query. If the desired
skills are supplied in a list, you have to count the number of items in the
list (which is one + number of coma, if coma are used as delimiters):

1+ len(InitialStringWithComa) -len(replace(InitialStringWithComa, ",",
"""" ))




Why SQL is difficult? maybe because it is based on 'sets' rather than
'arguments': SQL describes what result you want rather than, traditionally,
what the process has to be performed. A why SQL is doing that? because that
leaves the database engine to decide what will be the process, among known
processes to achieve the result, and let the database engine decided which
of these processes will be optimal to use.As example, in VBA, you will
probably code just ONE way to get a result, and each and every runs will
pass trough that solution, even if, in some cases, there could be better
alternatives. But to mimic SQL, you would probably write a solution if there
is an index, another solution if the table is small, another solution for
other cases..., and, to top all those solutions, a 'decider' which will
select among those coded solutions, allowing optimal solution to be selected
in each of the particular case. So, procedural VBA approach means the
developer 'decides' what kind of "typical" solution has to be taken, and
code that unique solution which will be used for all the cases, while
declarative SQL let the database engine to find a solution for a particular
case, case by case. But it is different, so it is new, and thus, 'harder' to
acquire, as skill, but easier to use, as 'code', once you got the skill...
at least, if you compare it to the equivalent VBA code, even if you were to
write just ONE procedure in VBA, to do what:

SELECT employee
FROM employees
WHERE skill IN( "a", "b", "cd")
GROUP BY employee
HAVING COUNT(*)=3

does. Sure, the VBA code may require less 'skill', but will you get the same
with four small lines of code? even in ten line of code? No, not really, SQL
is not really harder to use, once you got the skill to use it, as VBA code
is easy, ... well, once you know it. Note, for that purpose, that

1+ len(InitialStringWithComa) -len(replace(InitialStringWithComa, ",",
"""" ))

is VBA, not SQL. SQL way is to use a table, and eliminates the need of this
computation by using a sub-query, or DCount. Again, which one is easier?


Now, SQL is not a general language, and so, must not be used everywhere.
There are still places where VBA (or a procedural approach) is preferable.


Hoping it may help,
Vanderghast, Access MVP

 

[Robert Painter]

Michel

Thanks for message. Have been away for week but not forgotten to sort this
out AND have done it. Went all round the houses to get there (have been
counting pilots and planes in hangers (you know what i mean)) but have come
upwith the following and HEY it works a treat. Thanks for helping me on
this subject, your tuition was invaluable.


Private Sub cmdOK_Click()
' to carry out search of employee skills from multioption list boxes
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCategory As String
Dim strSkill As String
Dim strOption As Integer
Dim mySql As String
Dim skillcount As String
' getting choices from list box (note list categories not used)

For Each varItem In Me.LstSkill.ItemsSelected
strSkill = strSkill & "," & Me.LstSkill.ItemData(varItem)
Next varItem
' checking length of resulting string

If Len(strSkill) = 0 Then
strSkill = "Like '*'"
Else
strSkill = Right(strSkill, Len(strSkill) - 1)
strSkill = "IN(" & strSkill & ")"




' getting option group into stroption
strOption = Me.grpStatus

End If
skillcount = Me.LstSkill.ItemsSelected.count
Set db = CurrentDb
mySql = "SELECT DISTINCT
tblEmployees.Title,tblEmployees.EmployeeName,tblEmployees.FirstName,tblEmployees.HomePhone,tblEmployees.MobilePhone
" & _
"FROM tblEmployees LEFT JOIN tblEmployeeSkills " & _
"ON tblEmployees.EmployeeID = tblEmployeeSkills.EmployeeIDFK " & _
"WHERE tblEmployeeSkills.SkillIDFK " & strSkill & "" & _
" And tblEmployees.EmploymentStatus = " & strOption & "" & _
" GROUP BY Title,EmployeeName,FirstName,HomePhone,MobilePhone" & _
" HAVING COUNT(*) = " & skillcount & "" & _
" ORDER BY tblEmployees.EmployeeName,tblEmployees.FirstName"

'MsgBox mySql
Set qdf = db.QueryDefs("qryskillsListQuery")
qdf.SQL = mySql

'DoCmd.OpenQuery "qrySkillsListQuery"
DoCmd.OpenReport "qryskillslistquery", acViewPreview
'DoCmd.Close acForm, "frmSkillSListQuery"
Set qdf = Nothing
Set db = Nothing

End Sub

Hope i put the Skillcount = Me.LstSkill.ItemsSelected.count
in the right place. It works perfectly

Once again ThankYou

Robert

 

[Michel Walsh]

"(have been counting pilots and planes in hangers (you know what i
mean))"


Sure, sure, say hi to Joe.




Vanderghast, Access MVP