帮助查询... [英] Help with query...
问题描述
嘿伙计们，
我一直在考虑这个问题几天，还没有提出一个能给我预期结果的查询。我正在建立一个存储员工信息的数据库，我的老板要求我添加一个可以跟踪职位空缺的组件。
我有6个表都是链接的：
表：员工
1. ID;文本; PK
2. ...（其余与查询无关）
表：EmplStatus（链接到Employee.ID）
1.员工ID;文本; PK
2.日期;约会时间; PK
3. StatusCode;文字
4.有效;是/否
表：EmplOrganization（链接到Employee.ID）
1.员工ID;文本; PK
2.日期;约会时间; PK
3.部门代码;文字
4. DivisionCode;文字
5.有效;是/否
表：EmplJob（链接到Employee.ID和Job.Code）
1. EmployeeID;文本; PK
2.日期;约会时间; PK
3. JobCode;文字
4.有效;是/否
表：工作
1.代码;文本; PK
2.标题;文字
3. ...（其余与查询无关）
表：JobVacancy（链接到Job.Code）
1. JobCode;文本; PK
2. DepartmenCode;文本; PK
3. DivisionCode;文本; PK
4. NumberofPositions;数字
现在，我要做的是创建一个查询，它将显示作业表中的所有作业，每个作业的JobVacancy.NumberofPositions，所有员工相关联在特定部门/部门的特定职位。例如，我可能有20个区域工程师，其中5个在部门：A，部门：X，10个部门：B，部门：Y，5个部门：A，部门：Z。一些标准是：EmplStatus .Active = True，EmplJob.Active = True，EmplOrganization.Active = True，EmplStatus.StatusCode =''A''。
我正在寻找的最终结果是所有员工及其工作代码，部门和部门的清单。我得到的最接近的结果是5名员工离开了，我不知道为什么。在我的查询中需要员工ID的原因是我可以计算每个部门/部门中有多少员工的特定职位，以便我可以从职位数中减去以确定有多少空缺。这是我当前的SQL代码：
Hey guys,
I have been mulling over this problem for a few days and have yet to come up with a query that will give me the expected results. I am working on a database that stores employee information, and my boss has asked me to add a component that will keep track of job vacancies as well.
I have 6 tables that are all linked:
Table: Employee
1. ID; Text; PK
2. ... (the rest are irrelevant to the query)
Table: EmplStatus (linked to Employee.ID)
1. EmployeeID; Text; PK
2. Date; Date/Time; PK
3. StatusCode; Text
4. Active; Yes/No
Table: EmplOrganization (linked to Employee.ID)
1. EmployeeID; Text; PK
2. Date; Date/Time; PK
3. DepartmentCode; Text
4. DivisionCode; Text
5. Active; Yes/No
Table: EmplJob (linked to Employee.ID and Job.Code)
1. EmployeeID; Text; PK
2. Date; Date/Time; PK
3. JobCode; Text
4. Active; Yes/No
Table: Job
1. Code; Text; PK
2. Title; Text
3. ...(the rest are irrelevant to query)
Table: JobVacancy (linked to Job.Code)
1. JobCode; Text; PK
2. DepartmenCode; Text; PK
3. DivisionCode; Text; PK
4. NumberofPositions; Number
Now, what I''m trying to do is create a query that will show me all of the jobs in the job table, JobVacancy.NumberofPositions for each job, all employees associated with that particular job title in a particular department/division. For example, I may have 20 Area Engineers, where 5 are in Department: A, Division: X, 10 are in Department: B, Division: Y, and 5 are in Department: A, Division: Z. Some critera is: EmplStatus.Active=True, EmplJob.Active=True, EmplOrganization.Active=True, EmplStatus.StatusCode=''A''.
The end result that I''m looking for is a list of all employees and their job code, department, and division. The closest result that I have gotten leaves out 5 of the employees and I can''t figure out why. The reason for needing the employee ID''s in my query is so that I can do a count of how many employees are in each department/division with a certain job title, so that I can subtract that from the Number of Positions, in order to determine how many vacancies there are. Here is my current SQL code:
推荐答案
好的，我会的尝试简化这一点。以下查询为我提供了有关数据库中员工的预期结果：
Okay, I''ll try to simplify this a bit more. The following query gives me the expected results in regards to the employees in the database:
任何帮助将不胜感激。 ....
any help would be appreciated.....
也许您应该首先将其分解为多个查询并使每个查询都正常工作。现在很难遵循逻辑。
Perhaps you should break this down into multiple queries first and get each of those working. It''s hard to follow the logic right now.
这篇关于帮助查询...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
