Help with query...

[mlcampeau]

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: EmplOrganizatio n (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. NumberofPositio ns; 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.Numb erofPositions 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.Acti ve=True, EmplJob.Active= True, EmplOrganizatio n.Active=True, EmplStatus.Stat usCode='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:

[CODE=sql]SELECT DISTINCT Employee.ID, EmplJob.Employe eID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode, Department.Desc , Division.Desc, JobVacancy.Numb erofPositions, JobVacancy.Depa rtmenCode
FROM (Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.Employe eID) LEFT JOIN (Division RIGHT JOIN EmplOrganizatio n ON Division.Code = EmplOrganizatio n.DivisionCode) ON Employee.ID = EmplOrganizatio n.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.Empl oyeeID) ON Department.Code = EmplOrganizatio n.DepartmentCod e) ON Job.Code = EmplJob.JobCode ) LEFT JOIN JobVacancy ON Job.Code = JobVacancy.JobC ode
WHERE (((Employee.ID) =[EmplJob].[EmployeeID]) AND ((EmplJob.JobCo de) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode)=[EmplOrganizatio n].[DepartmentCode]) AND ((JobVacancy.Di visionCode)=[EmplOrganizatio n].[DivisionCode]) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((Employee.ID) =[EmplJob].[EmployeeID]) AND ((EmplJob.JobCo de) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Null) AND ((EmplOrganizat ion.DivisionCod e) Is Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode) Is Not Null) AND ((JobVacancy.Di visionCode) Is Not Null) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((Employee.ID) =[EmplJob].[EmployeeID]) AND ((EmplJob.JobCo de) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Not Null) AND ((EmplOrganizat ion.DivisionCod e) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode) Is Null) AND ((JobVacancy.Di visionCode) Is Null) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((Employee.ID) =[EmplJob].[EmployeeID]) AND ((EmplJob.JobCo de) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Null) AND ((EmplOrganizat ion.DivisionCod e) Is Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode) Is Null) AND ((JobVacancy.Di visionCode) Is Null) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobC ode) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode) Is Null) AND ((JobVacancy.Di visionCode)=[EmplOrganizatio n].[DivisionCode]) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobC ode) Is Not Null) AND ((EmplOrganizat ion.DivisionCod e) Is Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode)=[EmplOrganizatio n].[DepartmentCode]) AND ((JobVacancy.Di visionCode) Is Not Null) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobC ode) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Null) AND ((EmplOrganizat ion.DivisionCod e) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode) Is Not Null) AND ((JobVacancy.Di visionCode) Is Null) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobC ode) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Null) AND ((EmplOrganizat ion.DivisionCod e) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode) Is Null) AND ((JobVacancy.Di visionCode) Is Null) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobC ode) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Null) AND ((EmplStatus.St atusCode)='A') AND ((JobVacancy.De partmenCode) Is Not Null) AND ((JobVacancy.Di visionCode)=[EmplOrganizatio n].[DivisionCode]) AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobC ode) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Not Null) AND ((EmplOrganizat ion.DivisionCod e) Is Null) AND ((EmplStatus.St atusCode)='A') AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True)) OR (((EmplJob.JobC ode) Is Not Null) AND ((EmplOrganizat ion.DepartmentC ode) Is Null) AND ((EmplOrganizat ion.DivisionCod e) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True))
ORDER BY EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode;[/CODE]
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!

[mlcampeau]

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:
[CODE=sql]SELECT DISTINCT Employee.ID, EmplJob.Employe eID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode, Department.Desc , Division.Desc
FROM (Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.Employe eID) LEFT JOIN (Division RIGHT JOIN EmplOrganizatio n ON Division.Code = EmplOrganizatio n.DivisionCode) ON Employee.ID = EmplOrganizatio n.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.Empl oyeeID) ON Department.Code = EmplOrganizatio n.DepartmentCod e) ON Job.Code = EmplJob.JobCode ) LEFT JOIN JobVacancy ON Job.Code = JobVacancy.JobC ode
WHERE (((EmplJob.JobC ode) Is Not Null) AND ((EmplStatus.St atusCode)="A") AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True))
ORDER BY EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode;[/CODE]
This gives me one record for each employee who is assigned a job title. As soon as I add JobVacancy.Numb erofPositions 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.Div isionCode)=(Emp lOrganization.D ivisionCode), 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??

[mlcampeau]

any help would be appreciated.... .

[Rabbit]

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.

[mlcampeau]

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
[CODE=sql]SELECT JobVacancy.JobC ode, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions
FROM JobVacancy
ORDER BY JobVacancy.JobC ode, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode;[/CODE]
and MY - ActiveEmpJobs
[CODE=sql]SELECT Employee.ID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode
FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.Employe eID) LEFT JOIN (Division RIGHT JOIN EmplOrganizatio n ON Division.Code = EmplOrganizatio n.DivisionCode) ON Employee.ID = EmplOrganizatio n.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.Empl oyeeID) ON Department.Code = EmplOrganizatio n.DepartmentCod e) ON Job.Code = EmplJob.JobCode
GROUP BY Employee.ID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode, [EmplStatus.Acti ve], [EmplOrganizatio n.Active], [EmplJob.Active]
HAVING (((EmplJob.JobC ode) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True))
ORDER BY EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode;[/CODE]
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!

[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.

[mlcampeau]

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!!)

[Rabbit]

I've tried breaking it into 2 queries:
MY - JobVacancies
[CODE=sql]SELECT JobVacancy.JobC ode, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions
FROM JobVacancy
ORDER BY JobVacancy.JobC ode, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode;[/CODE]
and MY - ActiveEmpJobs
[CODE=sql]SELECT Employee.ID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode
FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.Employe eID) LEFT JOIN (Division RIGHT JOIN EmplOrganizatio n ON Division.Code = EmplOrganizatio n.DivisionCode) ON Employee.ID = EmplOrganizatio n.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.Empl oyeeID) ON Department.Code = EmplOrganizatio n.DepartmentCod e) ON Job.Code = EmplJob.JobCode
GROUP BY Employee.ID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode, [EmplStatus.Acti ve], [EmplOrganizatio n.Active], [EmplJob.Active]
HAVING (((EmplJob.JobC ode) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True))
ORDER BY EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode;[/CODE]
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.

[mlcampeau]

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).

[mlcampeau]

Would changing MY - ActiveEmpJobs to
[CODE=sql]SELECT Employee.ID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode
FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.Employe eID) LEFT JOIN (Division RIGHT JOIN EmplOrganizatio n ON Division.Code = EmplOrganizatio n.DivisionCode) ON Employee.ID = EmplOrganizatio n.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.Empl oyeeID) ON Department.Code = EmplOrganizatio n.DepartmentCod e) ON Job.Code = EmplJob.JobCode
WHERE (((EmplJob.JobC ode) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True))
ORDER BY EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode;[/CODE]
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?

[Rabbit]

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.

[mlcampeau]

Okay, so if I have MY - ActiveEmpJob
[CODE=sql]SELECT Employee.ID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode
FROM Job RIGHT JOIN (Department RIGHT JOIN (((Employee LEFT JOIN EmplJob ON Employee.ID = EmplJob.Employe eID) LEFT JOIN (Division RIGHT JOIN EmplOrganizatio n ON Division.Code = EmplOrganizatio n.DivisionCode) ON Employee.ID = EmplOrganizatio n.EmployeeID) LEFT JOIN EmplStatus ON Employee.ID = EmplStatus.Empl oyeeID) ON Department.Code = EmplOrganizatio n.DepartmentCod e) ON Job.Code = EmplJob.JobCode
GROUP BY Employee.ID, EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode, EmplStatus.Stat usCode, [EmplStatus.Acti ve], [EmplOrganizatio n.Active], [EmplJob.Active]
HAVING (((EmplJob.JobC ode) Is Not Null) AND ((EmplStatus.St atusCode)='A') AND (([EmplStatus.Acti ve])=True) AND (([EmplOrganizatio n.Active])=True) AND (([EmplJob.Active])=True))
ORDER BY EmplJob.JobCode , EmplOrganizatio n.DepartmentCod e, EmplOrganizatio n.DivisionCode;[/CODE]

and MY - JobVacancies
[CODE=sql]SELECT JobVacancy.JobC ode, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions
FROM JobVacancy
GROUP BY JobVacancy.JobC ode, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions
ORDER BY JobVacancy.JobC ode, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode;[/CODE]

then how would I get a query to get the results that I am looking for? I tried
[CODE=sql]SELECT [MY - ActiveEmpJob].ID, [MY - JobVacancies].JobCode, [MY - JobVacancies].DepartmenCode, [MY - JobVacancies].DivisionCode, [MY - JobVacancies].NumberofPositi ons
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;[/CODE]
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?

[Rabbit]

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.

[mlcampeau]

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.

[Rabbit]

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.