How to make this 'simple' query?

field- field-
surname town
---- ----

john NY
john Vegas
john Boston
eddie Boston
eddie New Orleans
eddie NY
burt Miami
burt Boston
hank NY

Hi all,
In the example you see the content of a table of persons with their favorit town
(the table consists of two fields, name and town)
How can a make a query that returns the persons who like certain towns?

for example: what persons like Boston and NY?
the answer is John and Eddie, BUT how do I do this in a query?
In real the table is much bigger and do I need to query with different towns
another example: who likes NY, Boston and Vegas, answer: john
(Sorry, maybe a simple question)

Best regards and thanks in advance,

Eddie Smit.
Holland

re: How to make this 'simple' query?

I think you should build the WHERE clause in code to allow an arbitrary
numbers of towns in the query if you want to be fancy. If not, try a
parameter query where you enter a list of towns:

SELECT Surname FROM MyTable WHERE Town IN ([Please enter towns])

Pavel

Eddie Smit wrote:[color=blue]
>
> field- field-
> surname town
> ---- ----
>
> john NY
> john Vegas
> john Boston
> eddie Boston
> eddie New Orleans
> eddie NY
> burt Miami
> burt Boston
> hank NY
>
> Hi all,
> In the example you see the content of a table of persons with their favorit town
> (the table consists of two fields, name and town)
> How can a make a query that returns the persons who like certain towns?
>
> for example: what persons like Boston and NY?
> the answer is John and Eddie, BUT how do I do this in a query?
> In real the table is much bigger and do I need to query with different towns
> another example: who likes NY, Boston and Vegas, answer: john
> (Sorry, maybe a simple question)
>
> Best regards and thanks in advance,
>
> Eddie Smit.
> Holland[/color]

re: How to make this 'simple' query?

thanks Pavel,

But in the parameter one can only give ONE town.
How can I check for two or more towns in SQL?
Perhaps I need to write a VB loop?

Thanks, eddie.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: How to make this 'simple' query?

eddiesmit@freeler.nl (Eddie Smit) wrote in
news:9d7d5799.0310080557.1fac903e@posting.google.c om:
[color=blue]
> field- field-
> surname town
> ---- ----
>
> john NY
> john Vegas
> john Boston
> eddie Boston
> eddie New Orleans
> eddie NY
> burt Miami
> burt Boston
> hank NY
>
> Hi all,
> In the example you see the content of a table of persons with their
> favorit town (the table consists of two fields, name and town)
> How can a make a query that returns the persons who like certain
> towns?
>
> for example: what persons like Boston and NY?
> the answer is John and Eddie, BUT how do I do this in a query?
> In real the table is much bigger and do I need to query with different
> towns another example: who likes NY, Boston and Vegas, answer: john
> (Sorry, maybe a simple question)[/color]

SELECT surname FROM tblFavTowns WHERE town in ('NY','Boston') GROUP BY surname HAVING
count(town)=2

for the more general case, if the towns are listed in tblTownList:

SELECT surname FROM tblFavTowns WHERE town in (SELECT town FROM tblTownList) GROUP BY
surname HAVING count(town) = (select count(town) from tblTownList)



--
Ross Presser -- rpresser AT imtek DOT com
.... seeking a new quote ...

re: How to make this 'simple' query?

Is it feasible for you to turn the list of towns into a list box with
MultiSelect enabled and build your query from the items the user selects?

Pavel

eddie smit wrote:[color=blue]
>
> thanks Pavel,
>
> But in the parameter one can only give ONE town.
> How can I check for two or more towns in SQL?
> Perhaps I need to write a VB loop?
>
> Thanks, eddie.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

re: How to make this 'simple' query?

One way to get at this in SQL is to use nested queries.

For example, to find out who likes Vegas, New York, and Boston, you could
use the following query:

SELECT Name
FROM tblFavCities
WHERE City = "Vegas" AND Name IN
(SELECT Name FROM tblFavCities WHERE City = "NY" and Name IN
(SELECT Name FROM tblFavCities WHERE City = "Boston"))

To break it down, the last query is evaluated first and it returns the
set of people with Boston as a favorite city. This would be John, Eddie
and Burt.

That list of people becomes a condition for the next query up, which then
returns the people who both match the list and have NY as a favorite
city. These are John and Eddie.

So John and Eddie are the ones passed on to the top query which returns
which of them is both on the list and has Vegas in their city list.
Finally, the query returns John.

To add another city to the list you would simply add an IN to the last
WHERE clause and slide in another subquery. Also, you can place
parameters in the slots for cities if you always wanted to search for a
set number of cities. Otherwise you could build this statement in code
fairly easily.

Hope that helps,

Carlos


eddiesmit@freeler.nl (Eddie Smit) wrote in
news:9d7d5799.0310080557.1fac903e@posting.google.c om:
[color=blue]
> field- field-
> surname town
> ---- ----
>
> john NY
> john Vegas
> john Boston
> eddie Boston
> eddie New Orleans
> eddie NY
> burt Miami
> burt Boston
> hank NY
>
> Hi all,
> In the example you see the content of a table of persons with their
> favorit town (the table consists of two fields, name and town)
> How can a make a query that returns the persons who like certain
> towns?
>
> for example: what persons like Boston and NY?
> the answer is John and Eddie, BUT how do I do this in a query?
> In real the table is much bigger and do I need to query with different
> towns another example: who likes NY, Boston and Vegas, answer: john
> (Sorry, maybe a simple question)
>
> Best regards and thanks in advance,
>
> Eddie Smit.
> Holland[/color]

re: How to make this 'simple' query?

using a multi-select listbox definitely gets my vote... even better,
there's code that helps you do most of this stuff right here...

http://www.mvps.org/access/forms/frm0007.htm

Use Multi-Select List boxes as query parameters
Author(s)
Dev Ashish


(Q) I have a MultiSelect listbox control on my form. I want to pass
the selected items to a query as a parameter. How do I do this?

(A) Unlike simple listbox controls which can be referenced as a
parameter by a query, MultiSelect listboxes cannot be used directly as
a parameter. This is because calling the listbox
(Forms!frmMyForm!lbMultiSelListBox) from anywhere will not
automatically concatenate all the selected items. You need to build
the criteria yourself.

Note: You can still use a parameterized query provided you pass the
entire Where clause to it via code as a parameter. (eg. Have the query
reference a hidden control to which you manually assign the complete
WHERE clause using the following logic.)

For example,

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Const cQUOTE As String = "'" '<--My addition (If you had dates,
it'd be #)

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]=" & cQUOTE
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & cQUOTE & " OR
[EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************

NOTE: In your case, because the town name is a string, you would have
to include a delimiter... You might want to read the original article
in case I've bollixed this up, but I don't think I have...