[help] Query with 1 or n parameter

[maurizio.ferracini]

a student know one ore more programming language

in a mask i select from a list_box one or more language and i send ad
parameter to a query that extract the student that know all this
language.

i use something like

table: Student,Language,Student- Language

query:
....
[Student- Language].Id_Lang
LikeNz([Forms]![Student-Query-param]![Lang_l_box],"*")

but if i select "ADA and C" the query report student that know :C or
ADA or C and ADA.

how i can do for got only the student that know "C *and* ADA"?.

grazie.

 

[Douglas J. Steele]

You can't, using that construct.

What does your data look like? From how you've described the table, it's
denormalized, so that you're storing multiple languages in a single field.
That's a recipe for disaster: you can't write reasonable queries against
that!

 

[maurizio.ferracini]

Douglas J. Steele ha scritto:

You can't, using that construct.

What does your data look like?

Student
id_student Name
--------------------------
0 Bob
1 Sara
2 Alex

Language
id_language Type
--------------------------
0 C
1 ADA
2 PERL

Student-Language
id_student it_language

 

[John Spencer]

The basic query would be as follows.
Assumption. Student-Lang table contains a record for each combination of
student and language.

So the basic query would be

SELECT Student.*
FROM Student
WHERE Student.StudentID IN
(SELECT T.StudentID
FROM [Student- Language] as T
WHERE T.ID_Lang in ("C","ADA")
GROUP BY T.StudentID
HAVING COUNT = 2)

 

[maurizio.ferracini]

John Spencer ha scritto:

The basic query would be as follows.
Assumption. Student-Lang table contains a record for each combination of
student and language.

So the basic query would be

SELECT Student.*
FROM Student
WHERE Student.StudentID IN
(SELECT T.StudentID
FROM [Student- Language] as T
WHERE T.ID_Lang in ("C","ADA")
GROUP BY T.StudentID
HAVING COUNT = 2)

ok but i need a parametric query not a static one

i try this (where Lang_l_box is a list_box where i can select one or
more language)

select [Student- Language].Id_Student
from [Student- Language]
where
[Student- Language].Id_Lang Like
Nz([Forms]![Student-Query-param]![Lang_l_box],"*")

but not work if i select "ADA and C" the query report student that know
:C or
ADA or C and ADA.

 

[John Spencer]

ok but i need a parametric query not a static one

Then you are going to have to build your query using VBA. I know of no way
to do this with a multi-select list box as a parameter. You can try to use
what I posted as a model.

John Spencer ha scritto:

The basic query would be as follows.
Assumption. Student-Lang table contains a record for each combination of
student and language.

So the basic query would be

SELECT Student.*
FROM Student
WHERE Student.StudentID IN
(SELECT T.StudentID
FROM [Student- Language] as T
WHERE T.ID_Lang in ("C","ADA")
GROUP BY T.StudentID
HAVING COUNT = 2)

ok but i need a parametric query not a static one

i try this (where Lang_l_box is a list_box where i can select one or
more language)

select [Student- Language].Id_Student
from [Student- Language]
where
[Student- Language].Id_Lang Like
Nz([Forms]![Student-Query-param]![Lang_l_box],"*")

but not work if i select "ADA and C" the query report student that know
:C or
ADA or C and ADA.