listbox - multiple selection

[VilMarci]

Hi,

I would like to solve a problem that seemed easy at first time...

I have a table (tblSkills) like:

ID,PersName,Language
1,Tom,English
2,Tom,German
3,John,German

I have a multiple selection listbox that now containes the gouped Language
coloumn of tblSkills.
How can I use this multiple selection box to find people with certain
languages skills? (I select English and German, and the query returns Tom in
this case).

Any ideas welcome,

Thanks,
VilMarci

 

[Michel Walsh]

Hi,



SELECT workers.PersName
FROM workers INNER JOIN wantedSkills
ON workers.Language = wantedSkills.Language
GROUP BY workers.PersName
HAVING COUNT(*)=(SELECT COUNT(*) FROM wantedSkills)



That assumes that there is no duplicated entries (no twice ..., Tom,
English ) in table workers AND a temporary table wantedSkills, again,
no duplicate, filled with the desired skills.


The inner join will remove un-wanted skills (unwanted records from table
workers), and thus, group-count must then match the number of skills we
desired, (SELECT COUNT(*) FROM wantedSkills), for the employee (PersName)
to be kept.




Hoping it may help,
Vanderghast, Access MVP

 

[VilMarci]

Hi Michel,

Thanks for the quick reply. I only miss one thing here: the multiple
selection box. How can I use this control to set the criterias for
(wantedSkills)?
I'd be very happy if you could give a sample code (or source) to get
started.

Thanks again,

Marton

 

[Michel Walsh]

Hi,


Start with http://www.mvps.org/access/forms/frm0007.htm, change the
statement in the loop for something line:

CurrentDb.Execute "INSERT INTO wantedSkills(Language) VALUES(""" &
ctl.ItemData(varItem) & """)"



Hoping it may help,
Vanderghast, Access MVP

 

[VilMarci]

Thanks Michel,

Now it's working just expected. I put the code to a button's onclick event,
and also inserted a line that clears the temp table and one that runs a
report, so the whole stuff runs seamless.

Thanks again,
Marton

 

[VilMarci]

Hi,

OK, one last thing...
Now if I don't select anything, the query result is nothing. Is it possible
to insert a condition to get all the records?
(I mean no selection - no filter)
Marton

 

[Michel Walsh]

Hi,


Maybe there is a much more elegant solution, but I would first try a
UNION ALL query:



SELECT workers.PersName
FROM workers INNER JOIN wantedSkills
ON workers.Language = wantedSkills.Language
GROUP BY workers.PersName
HAVING COUNT(*)=(SELECT COUNT(*) FROM wantedSkills)


UNION ALL

SELECT DISTINCT workers.PersName
FROM workers
WHERE 0=(SELECT COUNT(*) FROM wantedSkills)



As you observed, if wantedSkills table is empty, the first Select returns
nothing, just only the second select does. But if there is something in
wantedSkills, the second Select would return nothing, since then we will
have 0 <> (SELECT COUNT(*) ... )




Hoping it may help,
Vanderghast, Access MVP

 

[VilMarci]

I don't know if it's right, but yesterday I changed the join direction and
it looks like working... here's my sql:

SELECT pers_languages.ID
FROM WantedLangSkills RIGHT JOIN pers_languages ON WantedLangSkills.Nyelv =
pers_languages.Nyelv
GROUP BY pers_languages.ID
HAVING (((Count(WantedLangSkills.Nyelv))=(SELECT COUNT(*) FROM
WantedLangSkills)))
ORDER BY pers_languages.ID;

here my source table is the pers_languages that looks like: ID,Nyelv
(=language in HUN),rate
and the WantedLangSkills is the filter table with one field (Nyelv) that
containes the required skills. Is this good or may I encounter problems?

Marton

 

[Michel Walsh]

Hi,


Sounds to be fine, indeed.


Vanderghast, Access MVP