Union query from listboxes

[MrBitsy]

I have three tables that store data about different types of skill. An
employee table is linked to all three skills table as 1 to many links ie

1 employee has many skill 'a' records.
1 employee has many skill 'b' records.
1 employee has many skill 'c' records.

I am trying to design a form that contains three listboxes, each of which
will contain the users filter choices for that particular skill. Users can
select choices from combos for each listbox, so they can have 1 or more
search terms in each listbox

I now have these three listboxes, with the users choices and I now need to
fill an employee listbox with all employees that fit the filters in the
three listboxes ie

employee has skill 'a' or skill 'a' or skill 'a' from listbox 1
AND
employee has skill 'b' or skill 'b' or skill 'b' from listbox 2
AND
employee has skill 'c' or skill 'c' or skill 'c' from listbox 3

I am completely stumpped on how I should proceed with this form.

I thought of having 3 invisible listboxes that gets the results from each
skill listbox and 'merging' the results somehow? I then thought of creating
a table to hold the results from each listbox and then pulling the data out
of that table?

Anyone can point me in the right direction?

Thanks,

Ray Keattch.

 

[Rodrigo]

past this in the back of a form
assuming you have 1 command button and 3 list boxes, lbo0 and lbo1 have the
data and lbo3 shows the filtered data. And the listboxes bound field is a
number (you can change the code to work with text).

Basically, you call lbo1filter to give you a comma delimited list of all the
selected values, then you call lbo0filter to do the same for the other list
box, finally you call the filtersql to giveyou back the sql for query which
uses the 2 lists of values as the filter. You can change it anyway you want
it but the intent is to do the same.

Rodrigo.


Private Sub Command2_Click()
Call FilterSQL
'assumin lbo3 is the listbox with all the selected filters
Me.lbo3.RowSource = FilterSQL
Me.lbo3.Requery
End Sub

Private Function lbo1Filter() As String
Dim varItm As Variant
Dim strFilter As String
For Each varItm In Me.lbo1.ItemsSelected
strFilter = strFilter & Me.lbo1.ItemData(varItm) & ", "
Next varItm
strFilter = Left(strFilter, Len(strFilter) - 2)
lbo1Filter = strFilter
End Function

Private Function lbo0Filter() As String
Dim varItm As Variant
Dim strFilter As String
For Each varItm In Me.lbo0.ItemsSelected
strFilter = strFilter & Me.lbo0.ItemData(varItm) & ", "
Next varItm
strFilter = Left(strFilter, Len(strFilter) - 2)
lbo0Filter = strFilter
End Function

Private Function FilterSQL() As String
Dim strSql As String
Dim strFilter As String
strFilter = lbo1Filter & ", " & lbo0Filter

strSql = "SELECT tblEmployeesSkills.* from tblEmployeesSkills where SkillId
in (" & strFilter & ") ;"
FilterSQL = strSql
End Function

 

[John Vinson]

I have three tables that store data about different types of skill. An
employee table is linked to all three skills table as 1 to many links ie

1 employee has many skill 'a' records.
1 employee has many skill 'b' records.
1 employee has many skill 'c' records.

So if you add more skills you'll add more tables? I have to worry a
bit about this design! I'd suggest instead that you treat Employees
and Skills as a typical many to many relationship:

Employees
EmployeeID
<bio information>

Skills
SkillCode <Primary Key>
Description

EmployeeSkills
EmployeeID <link to Employees>
SkillCode <link to Skills>

I am trying to design a form that contains three listboxes, each of which
will contain the users filter choices for that particular skill. Users can
select choices from combos for each listbox, so they can have 1 or more
search terms in each listbox

I now have these three listboxes, with the users choices and I now need to
fill an employee listbox with all employees that fit the filters in the
three listboxes ie

employee has skill 'a' or skill 'a' or skill 'a' from listbox 1
AND
employee has skill 'b' or skill 'b' or skill 'b' from listbox 2
AND
employee has skill 'c' or skill 'c' or skill 'c' from listbox 3

I am completely stumpped on how I should proceed with this form.

I thought of having 3 invisible listboxes that gets the results from each
skill listbox and 'merging' the results somehow? I then thought of creating
a table to hold the results from each listbox and then pulling the data out
of that table?

Anyone can point me in the right direction?

I'm not at all sure I understand. "select choices from combos for each
listbox"????

A Listbox is a data display and editing tool. It's not a place to
store data. If you want to combine data, use a Query based on your
tables instead!

 

[MrBitsy]

So if you add more skills you'll add more tables?

There are three different types of skill.

Knowledge based
geographic based
Scored skills

Each of these three tables have quite different data so I did the
database as three seperate tables, linked to employees.

I have to worry a
bit about this design! I'd suggest instead that you treat Employees
and Skills as a typical many to many relationship:

Employees
EmployeeID
<bio information>

Skills
SkillCode <Primary Key>
Description

EmployeeSkills
EmployeeID <link to Employees>
SkillCode <link to Skills>
<maybe some other fields such as skill level = "expert", "novice">

This is exactly the design of my database but instead of 1
'EmployeeSkills' table there are three as outlined above.

'Employee' 1 to Many 'Geographic skills' Many to 1 'Skill'
'Employee' 1 to Many 'Scored Skills' Many to 1 'Skill'
'Employee' 1 to Many 'Knowledge based skill' Many to 1 'Skill'

I'm not at all sure I understand. "select choices from combos for each
listbox"????

Each listbox has an associated combobox to allow the choosing of skills
to search on. A filter value is chosen from the combo and its ID value
and text are passed into the associated list box. For each listbox, 1 or
more filters can be chosen from the combo.

The user can then press a search button to filter the data based on the
contents of the listboxes. A typical search might be, show me all
employees that speak french or german (listbox one), have worked in
Africa AND South America (Listbox 2) AND scored at least 3 in Electrical
Engineering AND 2 in Surveying (listbox 3).

Hope I made a little more sense that time!

Ray Keattch

 

[MrBitsy]

past this in the back of a form
assuming you have 1 command button and 3 list boxes, lbo0 and lbo1
have the data and lbo3 shows the filtered data. And the listboxes
bound field is a number (you can change the code to work with text).

Basically, you call lbo1filter to give you a comma delimited list of
all the selected values, then you call lbo0filter to do the same for
the other list box, finally you call the filtersql to giveyou back the
sql for query which uses the 2 lists of values as the filter. You can
change it anyway you want it but the intent is to do the same.

Rodrigo.

<Code snipped>

That is a very neat solution and it works a treat!

Thank you so much for the suggestion!

Ray Keattch.