If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A97 - Query from a Form not working
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"; |
#2
|
|||
|
|||
A97 - Query from a Form not working
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 "John" wrote: 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"; |
Thread Tools | |
Display Modes | |
|
|