Query/Filter problem

[BatHawk]

Help, I'm stuck!!!

I want users to be able to produce a report which lists the names of
those who meet certain criteria (i.e., the user selects up to 3 interests
using combo boxes and then produces a report which lists the names of
those who have those interests).

I can pull out all those who at least 1 or more of the interests selected,
but don't know how to write a query which
will return ONLY the names of those who have ALL the interests (i.e., to
exclude those who have less than the number of chosen interests)

PS: I thought i might be able to use the COUNT option to limit the result to
those who have all 3 interests only, but it all seems a bit beyond my skills
:-(

Tables as follows

tblContacts
Con_PK Con_Name etc, etc
7568 Smith
123 Jones
34569 Keen

tblInterest
Int_PK Int_Descrip
671234 Carpentry
1235 Gardening
1112 Plumbing
98356 Geology

tblInterest_Lookup
Lu_Con_FK Lu_Int_FK
7568 671234
123 1235
7568 1112
7568 98356
123 98356

select Con_Name as Contact, Int_Descrip as Interest from
(tblInterest JOIN tblInterest_Lookup ON tblInterest.Int_PK =
tblInterest_Lookup.Lu_Int_FK) INNER JOIN tbl_contacts ON
tblInterest_Lookup.Lu_Con_FK = tblContacts.Con_PK
where Int_Descrip like Forms!MyForm.cboInterest1 & "*" or
Int_Descrip like Forms!MyForm.cboInterest2 & "*" or
Int_Descrip like Forms!MyForm.cboInterest3 & "*"

 

[Guest]

Hi,

You need to use 'and' not 'or' in the where condition. And then you also
need to change the null value that you would get when no option is selected
to an empty string. Here is the modified query -

select Con_Name as Contact, Int_Descrip as Interest from
(tblInterest JOIN tblInterest_Lookup ON tblInterest.Int_PK =
tblInterest_Lookup.Lu_Int_FK) INNER JOIN tbl_contacts ON
tblInterest_Lookup.Lu_Con_FK = tblContacts.Con_PK
where Int_Descrip like Nz(Forms!MyForm.cboInterest1) & "*" and
Int_Descrip like Nz(Forms!MyForm.cboInterest2) & "*" and
Int_Descrip like Nz(Forms!MyForm.cboInterest3) & "*"


Jim

 

[Guest]

Hi,

Ignore my earlier post...it occurred to me as I hit post that I was too
quick (you obviously can't have the same field the same as 3 options!). I
was thinking you had something different than you do in the query. I have to
do leave for a few minutes, then I will repost with something that will
hopefully help!

Jim

 

[Guest]

Hi,

OK, here is how I would get around this. As you surmised, using a a count
is how to do it. The part of your original query that I changed is the
where condition.

What I did is that I made the where criteria a query that counts the number
of interests that match for each person, and limits the results returned to
be those whose count of matches is the same as the number of options entered.


select Con_Name as Contact, Int_Descrip as Interest from
(tblInterest JOIN tblInterest_Lookup ON tblInterest.Int_PK =
tblInterest_Lookup.Lu_Int_FK) INNER JOIN tbl_contacts ON
tblInterest_Lookup.Lu_Con_FK = tblContacts.Con_PK
where tblInterest_Lookup.Lu_Con_FK in (Select tblInterest_Lookup.Lu_Con_FK
From tblInterest_Lookup Inner Join
tblInterest on tblInterest_Lookup.Lu_Con_FK = tblInterest.Int_PK
where (tblInterest.Int_Descrip = Forms!MyForm.cboInterest1 or
tblInterest.Int_Descrip = Forms!MyForm.cboInterest2 or
tblInterest.Int_Descrip = Forms!MyForm.cboInterest3)
Group by tblInterest_Lookup.Lu_Con_FK
Having Count(tblInterest_Lookup.Lu_Con_FK) = IIf(Forms!MyForm.cboInterest1
Is Null,0,1)+
IIf(Forms!MyForm.cboInterest2 Is Null,0,1)+
IIf(Forms!MyForm.cboInterest3 Is Null,0,1)

Jim

 

[BatHawk]

Thanks Jim,
much appreciated ... that certainly solved the part of the problem. I will
need to think a bit about your solution though as it pulls out only those
who have exactly those interests and no more, ie., if someone has all
specified interests plus extras, they won't get returned. Need to put my
thinking cap on

 

[Guest]

Hi,

Actually it should work as you want even in that case. Give it a try.

Jim