My database is used to store emplyee skill training levels and some basic employee information. I have attached a screen shot of the tables used and their relationships. I am using a search form named [search_frm]. The form contains unbound comboboxes and a command button [Search] that runs the query named [search_frm_qry]. The query includes fields from three tables: data_tbl, Emp_tbl, and Skill_Cat_Old_tbl
If you need more details let me know.
My problem is that the query seems to ignore the [category] , [skill], and [level] fields. I've attached a screenshot of the table relationships.(access 2007) because I think that may be the problem. Thank you.
The relationships diagram shows the key on emp_tbl is Employee, not EmployeeID - this doesn't seem right?
I need to see the query string for search_frm_qry in order to attempt an answer to your question. What do the combo boxes have as their row source?
NickHa,
Thanks for replying. The combo boxes row sources are as follows:
Employee: SELECT DISTINCT Emp_tbl.Employee FROM Emp_tbl ORDER BY Emp_tbl.Employee;
Dept_Line: SELECT DISTINCT Emp_tbl.Dept_Line FROM Emp_tbl;
Shift: SELECT DISTINCT Emp_tbl.Shift FROM Emp_tbl ORDER BY Emp_tbl.Shift;
Category: SELECT DISTINCT Skills_tbl_OLD.CATID, Skills_tbl_OLD.Category FROM Skills_tbl_OLD;
Skill: Cbo_qry
*The Cbo_qry is used to populate this Skill combo box based on a choice in the Category combo box. This query is based on the skills_tbl_OLD table with these fields: [ID], [Skill], [CATID]. [CATID} has this criteria [Forms]![Search_frm]![CboCat]
Level: SELECT DISTINCT Data_tbl.Level FROM Data_tbl ORDER BY Data_tbl.Level;
I'm not sure what your queries are telling me here!
Some questions:
1. Why do you use DISTINCT in all of your queries? Do the tables have duplicate data (if so, why?)?
2. Is there a reason not to use the table key as the bound column for the combo boxes? For example, where you have
Employee: SELECT DISTINCT Emp_tbl.Employee FROM Emp_tbl ORDER BY Emp_tbl.Employee;
SELECT EmployeeID, Employee FROM Emp_tbl ORDER BY Employee;
because the EmployeeID field would give the unique record reference for use elsewhere (although this is dubious because the relationships dagram shows Employee as the PK).
3. In Skills_tbl_OLD, what is the difference between ID and CATID? ID is the record key (as indicated in the relationships diagram), so what is CATID?
4. What is the difference between RecID and ID in the Data_tbl?
5. Why is Employee the key for Emp_tbl and not EmployeeID?
6. What is in fields category and skill in tables Data_tbl and Skill_tbl_OLD? Are these foreign keys which point to a common data source? If so, what?
7. I tried to reconstruct the Cbo_qry from the information you have given:
Skill: Cbo_qry
*The Cbo_qry is used to populate this Skill combo box based on a choice in the Category combo box. This query is based on the skills_tbl_OLD table with these fields: [ID], [Skill], [CATID]. [CATID} has this criteria [Forms]![Search_frm]![CboCat]
"SELECT ID, Skill, CATID FROM skills_tbl_OLD WHERE CATID=" & [Forms]![Search_frm]![CboCat] & ";"
... is this correct? If so, why is CATID one of the fields returned when it is the search criterion? I'm just trying to understand how you use these fields.
On the face of it, it appears that your data are not structured appropriately, which is why you can't create the linkages you need, but it's hard to tell without knowing the overall structure and content of the table fields. In general, I would use the record key as the bound column for combo boxes, then use that as the link to other tables.
One practical suggestion is that you paste your queries into the SQL view of the query designer, so you can see what results are returned when you run the query manually. You can modify the query string to get what you need, then paste the query string back to the requisite combo box once it works as expected.
I learned that from someone a while back and now I have changed my syntax to what you've suggested and I'll use that from now on.
2.
Is there a reason not to use the table key as the bound column for the combo boxes? For example, where you haveI would expect to see
Code:
SELECT EmployeeID, Employee FROM Emp_tbl ORDER BY Employee;
because the EmployeeID field would give the unique record reference for use elsewhere (although this is dubious because the relationships dagram shows Employee as the PK).
Yes they are FK that point to the fields with the same names in the Skill_tbl_OLD.
7.
I tried to reconstruct the Cbo_qry from the information you have given:which gives me this
Code:
"SELECT ID, Skill, CATID FROM skills_tbl_OLD WHERE CATID=" & [Forms]![Search_frm]![CboCat] & ";"
... is this correct? If so, why is CATID one of the fields returned when it is the search criterion? I'm just trying to understand how you use these fields.
I will have to take another look at this query now that CATID is deleted from the Skills_tbl_OLD
Thank you for your help. I"ll keep plugging away. I could tell my tables aren't linked properly. I need to mention that my Search_form data property is set as the following:
Code:
SELECT [Emp_tbl].[Employee], [Emp_tbl].[Dept_Line], [Emp_tbl].[Shift], [Data_tbl].[Category], [Skills_tbl_OLD].[Skill], [Data_tbl].[Level] FROM (Data_tbl INNER JOIN Skills_tbl_OLD ON Data_tbl.Skill=Skills_tbl_OLD.Skill) INNER JOIN Emp_tbl ON Data_tbl.Employee=Emp_tbl.Employee;
