Help with query...

296 Expert 100+

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:

Expand|Select|Wrap|Line Numbers

 SELECT DISTINCT Employee.ID, EmplJob.EmployeeID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode, Department.Desc, Division.Desc, JobVacancy.NumberofPositions, JobVacancy.DepartmenCode

FROM (Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode) LEFT JOIN JobVacancy ON Job.Code = JobVacancy.JobCode

WHERE (((Employee.ID)=[EmplJob].[EmployeeID]) AND ((EmplJob.JobCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode)=[EmplOrganization].[DepartmentCode]) AND ((JobVacancy.DivisionCode)=[EmplOrganization].[DivisionCode]) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((Employee.ID)=[EmplJob].[EmployeeID]) AND ((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Null) AND ((EmplOrganization.DivisionCode) Is Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode) Is Not Null) AND ((JobVacancy.DivisionCode) Is Not Null) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((Employee.ID)=[EmplJob].[EmployeeID]) AND ((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Not Null) AND ((EmplOrganization.DivisionCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode) Is Null) AND ((JobVacancy.DivisionCode) Is Null) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((Employee.ID)=[EmplJob].[EmployeeID]) AND ((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Null) AND ((EmplOrganization.DivisionCode) Is Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode) Is Null) AND ((JobVacancy.DivisionCode) Is Null) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode) Is Null) AND ((JobVacancy.DivisionCode)=[EmplOrganization].[DivisionCode]) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DivisionCode) Is Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode)=[EmplOrganization].[DepartmentCode]) AND ((JobVacancy.DivisionCode) Is Not Null) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Null) AND ((EmplOrganization.DivisionCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode) Is Not Null) AND ((JobVacancy.DivisionCode) Is Null) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Null) AND ((EmplOrganization.DivisionCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode) Is Null) AND ((JobVacancy.DivisionCode) Is Null) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Null) AND ((EmplStatus.StatusCode)='A') AND ((JobVacancy.DepartmenCode) Is Not Null) AND ((JobVacancy.DivisionCode)=[EmplOrganization].[DivisionCode]) AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Not Null) AND ((EmplOrganization.DivisionCode) Is Null) AND ((EmplStatus.StatusCode)='A') AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobCode) Is Not Null) AND ((EmplOrganization.DepartmentCode) Is Null) AND ((EmplOrganization.DivisionCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True))

ORDER BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode;

If anyone can help me to try get the query to display the expected results I'd appreciate it. The problem I'm having is making sure that the employee ID only shows up once in the query result - I need the employee to only show up with the job code, department and division that they are associated with. I am having troubles where some job codes might be associated with more than one department and division - in those cases, I have had results where an employee ID will show up the same number of occurrences as there are different department/divisions associated with the job code. I hope someone can make sense of this and try to help me out! Thanks!

Jul 23 '07 #1

Subscribe Post Reply

2754

mlcampeau

296

Expert 100+

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:

Expand|Select|Wrap|Line Numbers

 SELECT DISTINCT Employee.ID, EmplJob.EmployeeID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode, Department.Desc, Division.Desc

FROM (Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode) LEFT JOIN JobVacancy ON Job.Code = JobVacancy.JobCode

WHERE (((EmplJob.JobCode) Is Not Null) AND ((EmplStatus.StatusCode)="A") AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True))

ORDER BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode;

This gives me one record for each employee who is assigned a job title. As soon as I add JobVacancy.NumberofPositions to the SELECT clause, my 611 results turns into 983 results because if there is a job that is in more than one department or division, an employee associated with that job will show up for each department/division. For example - a Divisional Accountant can be in Department A, Division X, or Department A, Division Y, or Department A, Division Z. I have 2 positions for division X, 6 positions for division y, and 9 positions for division Z. Therefore, Employee 1234 who is actually in Department A, Division X, will also show up under Division Y and Division Z, which is incorrect. If I try WHERE (JobVacancy.DivisionCode)=(EmplOrganization.Divisi onCode), that won't work either, because there are cases where there is a position available, but no employees are assigned to it, so it won't show up in the query in that case. Anyone have any ideas??

Jul 24 '07 #2

mlcampeau

296

Expert 100+

any help would be appreciated.....

Jul 24 '07 #3

Rabbit

12,516

Expert Mod 8TB

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.

Jul 25 '07 #4

mlcampeau

296

Expert 100+

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.

I've tried breaking it into 2 queries:
MY - JobVacancies

Expand|Select|Wrap|Line Numbers

 SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions

FROM JobVacancy

ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

and MY - ActiveEmpJobs

Expand|Select|Wrap|Line Numbers

 SELECT Employee.ID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode

FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode

GROUP BY Employee.ID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode, [EmplStatus.Active], [EmplOrganization.Active], [EmplJob.Active]

HAVING (((EmplJob.JobCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True))

ORDER BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode;

Each query gives the expected results - the first lists all jobs in the JobVacancy table, the dept, div, and # of positions
1. Accountant; Business Services; Accounts Payable; 5
2. Accountant; Business Services; Timb Support; 4
3. Engineer; Timb; CRR; 3
4. Engineer; Timb; WRR; 6
etc.
The second query lists all Active employeeIDs, their job, department, division
1. 1234; Accountant; Business Services; Accounts Payable
2. 2345; Accountant; Business Services; Accounts Payable
3. 3456; Accountant; Business Services; Timb Support
etc.
What I'm trying to do now, is to create a query that will basically combine these two queries. I want it to show all Job Titles in the database (so all jobs in the first query - there isn't necessarily a related record in the second query), all departments and divisions associated with that job (once all data is entered correctly, it should be all the departments and divisions in the first query, but there are cases where an employee has a job title in a particular department or division, but that position is not entered in the Job Vacancy table or the position is, but not associated with that department or division (i.e. looking at above example - an employee may be an Engineer, in Timb, at NVIR - which is not a record in the job vacancy table)). I also want it to show the number of positions. I don't care if the query produces a record for each individual employee (there will be cases where there are jobs in the job vacancy table, but no employees are associated with that position because it is a vacant position), or if the query does a count of the number of employees with that position in that particular department and division.
So my result would be :
EmpID; Job; Dept; Div; #Positions
1. 1234; Accountant, Business Services, Accounts Payable; 5
2. 2345; Accountant, Business Services; Accounts Payable; 5
3. 3456; Accountant, Business Services, Timb Support; 4
etc.
The problem I'm having is that I'm getting results like:
1. 1234; Accountant, Business Services, Accounts Payable; 5
2. 1234; Accountant, Business Services, Timb Support; 4
3. 2345; Accountant, Business Services, Accounts Payable; 5
4. 2345; Accountant, Business Services, Timb Support; 4
5. 3456; Accountant, Business Services, Accounts Payable; 5
6. 3456; Accountant, Business Services, Timb Support; 4

I'm sorry it's so confusing!!! I'm trying my best to explain it in a way that you can understand. Anymore questions, let me know!

Jul 25 '07 #5

RZ15

I'm no expert and I can't read the joins in your SQL, but it seems there is something wrong with the join you have between the employee table and the emplJob table. It looks like the join should be an inner join between the PK and FK for employee ID between the two tables (EDIT: or maybe an outer join from the jobs table to the employees so it will return jobs that are vacant).

I would suggest doing what Rabbit said. Try doing a query in which you pull the Employee ID, division code and department code. Get the join on that to be correct and give you the desired results, and then build off that.

EDIT: I think I misunderstood, in which case my post will not help you.

Jul 25 '07 #6

mlcampeau

296

Expert 100+

I'm no expert and I can't read the joins in your SQL, but it seems there is something wrong with the join you have between the employee table and the emplJob table. It looks like the join should be an inner join between the PK and FK for employee ID between the two tables.

I would suggest doing what Rabbit said. Try doing a query in which you pull the Employee ID, division code and department code. Get the join on that to be correct and give you the desired results, and then build off that.

I just posted the queries in which the desired results are given in the post above yours. It doesn't let me change the join that you suggested. I built the query is design view, rather than writing the sql myself, so I'm not sure that that is the problem (although I'm really no expert!!)

Jul 25 '07 #7

Rabbit

12,516

Expert Mod 8TB

I've tried breaking it into 2 queries:
MY - JobVacancies

Expand|Select|Wrap|Line Numbers

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions

FROM JobVacancy

ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

and MY - ActiveEmpJobs

Expand|Select|Wrap|Line Numbers

SELECT Employee.ID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode

FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode

GROUP BY Employee.ID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode, [EmplStatus.Active], [EmplOrganization.Active], [EmplJob.Active]

HAVING (((EmplJob.JobCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True))

ORDER BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode;

Each query gives the expected results - the first lists all jobs in the JobVacancy table, the dept, div, and # of positions
1. Accountant; Business Services; Accounts Payable; 5
2. Accountant; Business Services; Timb Support; 4
3. Engineer; Timb; CRR; 3
4. Engineer; Timb; WRR; 6
etc.
The second query lists all Active employeeIDs, their job, department, division
1. 1234; Accountant; Business Services; Accounts Payable
2. 2345; Accountant; Business Services; Accounts Payable
3. 3456; Accountant; Business Services; Timb Support
etc.
What I'm trying to do now, is to create a query that will basically combine these two queries. I want it to show all Job Titles in the database (so all jobs in the first query - there isn't necessarily a related record in the second query), all departments and divisions associated with that job (once all data is entered correctly, it should be all the departments and divisions in the first query, but there are cases where an employee has a job title in a particular department or division, but that position is not entered in the Job Vacancy table or the position is, but not associated with that department or division (i.e. looking at above example - an employee may be an Engineer, in Timb, at NVIR - which is not a record in the job vacancy table)). I also want it to show the number of positions. I don't care if the query produces a record for each individual employee (there will be cases where there are jobs in the job vacancy table, but no employees are associated with that position because it is a vacant position), or if the query does a count of the number of employees with that position in that particular department and division.
So my result would be :
EmpID; Job; Dept; Div; #Positions
1. 1234; Accountant, Business Services, Accounts Payable; 5
2. 2345; Accountant, Business Services; Accounts Payable; 5
3. 3456; Accountant, Business Services, Timb Support; 4
etc.
The problem I'm having is that I'm getting results like:
1. 1234; Accountant, Business Services, Accounts Payable; 5
2. 1234; Accountant, Business Services, Timb Support; 4
3. 2345; Accountant, Business Services, Accounts Payable; 5
4. 2345; Accountant, Business Services, Timb Support; 4
5. 3456; Accountant, Business Services, Accounts Payable; 5
6. 3456; Accountant, Business Services, Timb Support; 4

I'm sorry it's so confusing!!! I'm trying my best to explain it in a way that you can understand. Anymore questions, let me know!

Your results come out that way because you're trying to join an individual level query with an aggregate query. So you get the aggregate results for each individual, they're meant to be separate.

So my question is what is the end result you're looking for.

Jul 25 '07 #8

mlcampeau

296

Expert 100+

The end result I'm looking for would be a list of every job code in the job vacancy table, along with the department, and division, and number of positions (just like the MY - JobVacancies query), with another column showing a count of the number of employees with that job code, department and division, that way I can do a calculation to determine how many vacancies are available for each position, and where the position is (department and division).

Jul 25 '07 #9

mlcampeau

296

Expert 100+

Would changing MY - ActiveEmpJobs to

Expand|Select|Wrap|Line Numbers

 SELECT Employee.ID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode

FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode

WHERE (((EmplJob.JobCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True))

ORDER BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode;

make it a non-aggregate query? Because this gives the same results as the query I stated above. (I just removed the Group By clause) Would this make it easier to combine the two to get the desired results?

Jul 25 '07 #10

Rabbit

12,516

Expert Mod 8TB

In the end then, you're looking for an aggregate query. You need to get that individual query and make it an aggregate query before joining it to the vacancies query to get your desired results.

Jul 25 '07 #11

mlcampeau

296

Expert 100+

Okay, so if I have MY - ActiveEmpJob

Expand|Select|Wrap|Line Numbers

 SELECT Employee.ID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode

FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode

GROUP BY Employee.ID, EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode, [EmplStatus.Active], [EmplOrganization.Active], [EmplJob.Active]

HAVING (((EmplJob.JobCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True))

ORDER BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode;

and MY - JobVacancies

Expand|Select|Wrap|Line Numbers

 SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions

FROM JobVacancy

GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions

ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

then how would I get a query to get the results that I am looking for? I tried

Expand|Select|Wrap|Line Numbers

 SELECT [MY - ActiveEmpJob].ID, [MY - JobVacancies].JobCode, [MY - JobVacancies].DepartmenCode, [MY - JobVacancies].DivisionCode, [MY - JobVacancies].NumberofPositions

FROM [MY - JobVacancies] LEFT JOIN [MY - ActiveEmpJob] ON [MY - JobVacancies].JobCode = [MY - ActiveEmpJob].JobCode

ORDER BY [MY - JobVacancies].JobCode, [MY - JobVacancies].DepartmenCode, [MY - JobVacancies].DivisionCode;

but in cases where a job is in more than one department or division, an employee shows up that many times
i.e. - Job; Dept; Div; #Positions
Divisional Accountant; BS; FSS; 2
Divisional Accountant; BS; MANS; 6
Divisional Accountant; BS; TIMBS; 9
There are 17 positions listed above, so if employee 1234 was a divisional accountant, they would show up in all 3 lines
1234;Divisional Accountant; BS; FSS; 2
1234;Divisional Accountant; BS; MANS; 6
1234;Divisional Accountant; BS; TIMBS; 9
So in this case, since there's 17 positions, Divisional Accountant actually shows up 17*3 times, and each employee who is a Divisional Accountant shows up once per division. I hope that makes sense. Any ideas how to do this? Or should I just be using two queries and setting up my forms and reports based on 2 queries?

Jul 25 '07 #12

Rabbit

12,516

Expert Mod 8TB

You shouldn't have the Employee ID as one of the fields in your first query. You should be doing a count and grouping by the same variables as your second query. Then you have one query that gives you the counts of people active in a job and the other query gives you how many positions are available for that job.

Jul 25 '07 #13

mlcampeau

296

Expert 100+

Hmm...I tried taking EmployeeID out of my first query and instead of the 611 records that it should produce, it only shows 453 (1 record per job code, rather than 1 record per employee) so I don't see how I'll be able to do an employee count that way.

Jul 25 '07 #14

Rabbit

12,516

Expert Mod 8TB

Did you include a count field? Because rather than returning the employees and then doing a count, what I'm having you do is jump straight to the count.

Jul 25 '07 #15

mlcampeau

296

Expert 100+

Okay, I tried this. It gives me the correct counts.

Expand|Select|Wrap|Line Numbers

 SELECT EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode, Count(Employee.ID) AS CountOfID

FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.EmployeeID) LEFT JOIN (Division RIGHT JOIN EmplOrganization ON Division.Code = EmplOrganization.DivisionCode) ON Employee.ID = EmplOrganization.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.EmployeeID) ON Department.Code = EmplOrganization.DepartmentCode) ON Job.Code = EmplJob.JobCode

GROUP BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode, EmplStatus.StatusCode, [EmplStatus.Active], [EmplOrganization.Active], [EmplJob.Active]

HAVING (((EmplJob.JobCode) Is Not Null) AND ((EmplStatus.StatusCode)='A') AND (([EmplStatus.Active])=True) AND (([EmplOrganization.Active])=True) AND (([EmplJob.Active])=True))

ORDER BY EmplJob.JobCode, EmplOrganization.DepartmentCode, EmplOrganization.DivisionCode;

My second query is

Expand|Select|Wrap|Line Numbers

 SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions

FROM JobVacancy

GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions

ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

I then tried to make a query using both of them:

Expand|Select|Wrap|Line Numbers

 SELECT [MY - JobVacancies].JobCode, [MY - JobVacancies].DepartmenCode, [MY - JobVacancies].DivisionCode, [MY - JobVacancies].NumberofPositions, [MY - ActiveEmpJob2].CountOfID

FROM [MY - ActiveEmpJob2] INNER JOIN [MY - JobVacancies] ON [MY - ActiveEmpJob2].JobCode = [MY - JobVacancies].JobCode

GROUP BY [MY - JobVacancies].JobCode, [MY - JobVacancies].DepartmenCode, [MY - JobVacancies].DivisionCode, [MY - JobVacancies].NumberofPositions, [MY - ActiveEmpJob2].CountOfID;

This gives me the same problem as before - for each department and division, instead of showing the employee numbers, it shows the count of ID more than once
i.e. - Job; Dept; Div; #Positions; CountofID
Accountant; BS; FSS; 2; 2
Accountant; BS; FSS; 2; 6
Accountant; BS; FSS; 2; 9
Accountant; BS; MANS; 6; 2
Accountant; BS; MANS; 6; 6
Accountant; BS; MANS; 6; 9
Accountant; BS; TIMBS; 9; 2
Accountant; BS; TIMBS; 9; 6
Accountant; BS; TIMBS; 9; 9

When it should be:
Accountant; BS; FSS; 2; 2
Accountant; BS; MANS; 6; 6
Accountant; BS; TIMBS; 9; 9
But I can't have criteria that #Positions=CountofID because they aren't always going to be the same. So frustrating!!!

Jul 25 '07 #16

Rabbit

12,516

Expert Mod 8TB

Your last query should be:

Expand|Select|Wrap|Line Numbers

 
SELECT [MY - JobVacancies].JobCode, [MY - JobVacancies].DepartmenCode, [MY - JobVacancies].DivisionCode, NumberofPositions, CountOfID, (NumberofPositions - CountOfID) As Vacancy

FROM [MY - ActiveEmpJob2] INNER JOIN [MY - JobVacancies] ON ([MY - ActiveEmpJob2].JobCode = [MY - JobVacancies].JobCode) AND ([MY - ActiveEmpJob2].DepartmenCode= [MY - JobVacancies].DepartmenCode) AND ([MY - ActiveEmpJob2].DivisionCode= [MY - JobVacancies].DivisionCode);

Althought I suspect you may want to use one of the outer joins instead.

Jul 25 '07 #17

mlcampeau

296

Expert 100+

I tried the query you suggested - I have gotten similar results before (minus the calculation of vacancies), but they aren't quite accurate. Everything it produces is correct, but it isn't showing those jobs that haven't been entered into the Job Vacancy table but have an employee with that job title, or the jobs where the department and/or division hasn't been filled in for the employee. In a perfect world where I had all the information to fill those blanks out, your query would work, but there's a reason why this database is being built. The company has bought out a few other companies and has thousands of employees, and we're still in the stages of figuring out who people are, where they're at, etc. Finding the information has been quite a task and I still don't have it all!! The goal is to have all the info entered for each employee and in the job vacancy table, but reality is, that info just isn't at hand right now and I need to work with what I have, so I'm trying to make my form and report function with this in mind. I think I'll be calling it quits for the day here and try this again tomorrow....Thanks for all the efforts!

Jul 25 '07 #18

Rabbit

12,516

Expert Mod 8TB

I tried the query you suggested - I have gotten similar results before (minus the calculation of vacancies), but they aren't quite accurate. Everything it produces is correct, but it isn't showing those jobs that haven't been entered into the Job Vacancy table but have an employee with that job title, or the jobs where the department and/or division hasn't been filled in for the employee. In a perfect world where I had all the information to fill those blanks out, your query would work, but there's a reason why this database is being built. The company has bought out a few other companies and has thousands of employees, and we're still in the stages of figuring out who people are, where they're at, etc. Finding the information has been quite a task and I still don't have it all!! The goal is to have all the info entered for each employee and in the job vacancy table, but reality is, that info just isn't at hand right now and I need to work with what I have, so I'm trying to make my form and report function with this in mind. I think I'll be calling it quits for the day here and try this again tomorrow....Thanks for all the efforts!

In that case you use a RIGHT JOIN And Nz(NumberofPositions, 0) - Nz(CountOfID, 0) instead.

Jul 26 '07 #19

mlcampeau

296

Expert 100+

Thanks so much! It ended up being a left join rather than a right join, but I got the results I was looking for!! I've never used the Nz before, but it works great! After a week of trying to figure that query out, it's a huge relief! Thanks again!

Jul 26 '07 #20

Rabbit

12,516

Expert Mod 8TB

Not a problem, good luck.

Nz(Variable, Value) will take the Variable and if it's null, return the value. The thing with nulls is whenever there's a null in an expression, the expression returns null. So you have to give the null a value.

Jul 26 '07 #21

dfw1417

I know I am a little late and probably bringing a lot less knowledge of access and experience than others here, however from what I gather, if you have not already done this one of the things I would ensure is in place is that EACH JOB POSITION which exists has a UNIQUE Identifying number. This would enable you to include all the job positions regardless of whether they are filled or vacant and then you could relate or join or whatever to create the list of total job positions and each employee which fills each unique postion leaving you the ability to create a list for those positions which are not filled. Just a thought... Im a newbie here.

Jul 27 '07 #22

Rabbit

12,516

Expert Mod 8TB

That would be how you would normally design the tables but from the sounds of it they inherited the database and had to make do. But also, they've expanded so much that they don't even know how many positions are available at this point in time.

Jul 27 '07 #23

mlcampeau

296

Expert 100+

I know I am a little late and probably bringing a lot less knowledge of access and experience than others here, however from what I gather, if you have not already done this one of the things I would ensure is in place is that EACH JOB POSITION which exists has a UNIQUE Identifying number. This would enable you to include all the job positions regardless of whether they are filled or vacant and then you could relate or join or whatever to create the list of total job positions and each employee which fills each unique postion leaving you the ability to create a list for those positions which are not filled. Just a thought... Im a newbie here.

That was one of the options that I had thought of, but as it stands, there are 241 job titles in my database - for each job that has more than one pay grade associated with it, I have assigned a unique job code (i.e. - Divisional Accountant, Pay Grade 10, JobCode: 70101A, Divisional Accountant, Pay Grade 11, Job Code: 70101B, etc.). If I were to do each JOB POSITION, that table would be absolutely huge!! In some cases I have 20 positions associated with a job title, etc. The main purpose of this database is to act as an HRIS system, so it is employee oriented. My boss has asked that I include the job vacancy component to it, which was a bit of a headache because, as I said, the system is employee oriented, not job oriented. Anyway, I worked my way around it. I ended up having to use 2 different queries, but I got the results I was looking for! And Rabbit is right, this database was inherited, so I sort of had to work with what I had, and at this point, I'm still not entirely sure how many job positions truly exist and even if I did, it can easily change on a day to day basis.

Jul 27 '07 #24

Similar topics