A97 - Query from a Form not working

[John]

I have a table with about 70 "skills" which are based
on an employee survey (to be used for an emergency)
such as Electrician or Roofer or many others

Each skill will be blank or have an X

I have a MainMenu form with a ListBox that is linked
to a list of skills, so the emergency Coordinator may
select a particular skill, and then print a report

I have created a Query (code below) which is SUPPOSED
to then select out of the Employee table when the skill
has an X in the individual, selected data field (I cut
a lot out of the query, since it is so long)

My IDEA is that the where clause will use the selection
from the form, so if I select ROOFER on the form, the
where clause will check the ROOFER field in the employee
table, and return all records where there is an X

It is not working... no errors, just nothing shows

Any help is appreciated

SELECT Employee.RealName, Employee.Myidnum, Employee.WsuTitle,
Employee.WsuDiv, Employee.PhoneHome, Employee.PhoneCell,
Employee.Email, Employee.MyAdrs, Employee.Miles, Employee.Crisis,
Employee.YearCrisis, Employee.EmtCert, Employee.YearEmtCert,
Employee.FirstAid, Employee.YearFirstAid, Employee.Nurse
FROM Employee
WHERE [forms]![MainMenu]![SelectSkill] = "X";

 

[Ken Sheridan]

Your problems stem from a fundamental design flaw. By having a separate
column in the table per skill you are doing what's known as 'encoding data as
column headings'. In a relational database data should be stored only as
values at column positions in rows in tables.

You need to decompose your table into three normalized tables:

1. Employees: this will have columns such as EmployeeID, FirstName, LastName
etc representing each attribute type of the employees entity type.
EmployeeID should be a unique numeric column as the table's primary key, e.g.
an autonumber.

2. Skills: This needs just the one column Skill as the values for this
will be unique. The column is the table's primary key of course.

3. EmployeeSkills: This table models the many-to-many relationship type
between Employees and Skills and will have foreign key columns Employee and
Skill, each referencing the primary keys of the other two tables. Together
these two columns form the primary key of EmployeeSkills. There might be
other non-key columns such as SkillLevel etc.

With you list box of skills all that's now necessary to return employees
with the selected skill is to join the Employees EmployeeSkills table and
refernce the list box as a parameter:

SELECT FirstName, LastName, Skill
FROM Employees INNER JOIN EmployeeSkills
ON Skills.EmployeeID = Employees.EmployeeID
WHERE Skill = [forms]![MainMenu]![SelectSkill];

If you wished you could use a multi-select list box so more than one skill
could be selected and a report opened for those employees with one or more of
those skills. It would probably be most appropriate to group the report by
Skill so that under each skill a set of employees would be listed. In this
case the report's query needs no parameter, but is filtered when opened from
a button on the form with the list box. The code for the button's Click
event procedure would go like this:

Dim varItem As Variant
Dim strSkillList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.SelectSkill

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSkillList = strSkillList & ",""" & ctrl.ItemData(varItem) &
""""
Next varItem

' remove leading comma
strSkillList = Mid(strSkillList, 2)

strCriteria = "Skill In(" & strSkillList & ")"

DoCmd.OpenReport "[YourReport]", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No skills selected", vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

I have a table with about 70 "skills" which are based
on an employee survey (to be used for an emergency)
such as Electrician or Roofer or many others

Each skill will be blank or have an X

I have a MainMenu form with a ListBox that is linked
to a list of skills, so the emergency Coordinator may
select a particular skill, and then print a report

I have created a Query (code below) which is SUPPOSED
to then select out of the Employee table when the skill
has an X in the individual, selected data field (I cut
a lot out of the query, since it is so long)

My IDEA is that the where clause will use the selection
from the form, so if I select ROOFER on the form, the
where clause will check the ROOFER field in the employee
table, and return all records where there is an X

It is not working... no errors, just nothing shows

Any help is appreciated

SELECT Employee.RealName, Employee.Myidnum, Employee.WsuTitle,
Employee.WsuDiv, Employee.PhoneHome, Employee.PhoneCell,
Employee.Email, Employee.MyAdrs, Employee.Miles, Employee.Crisis,
Employee.YearCrisis, Employee.EmtCert, Employee.YearEmtCert,
Employee.FirstAid, Employee.YearFirstAid, Employee.Nurse
FROM Employee
WHERE [forms]![MainMenu]![SelectSkill] = "X";