Comparison Query (1 Viewer)

[Coleman984]

I don't know exactly what I need in terms of how it is done in access. In real word terms what I need is a query to show how many employees are scheduled by department.

How do I create a query that will count the number of employees in that department that aren't off on that particular day? If 7 queries need to be created (one for each day) that would be fine as they could be strung together with a form.

Now this is the setup.
table tblEmployee with the following fields. This table contains the information that will link the other two tables:

pkEmployeeID as AutoNumber
txtFName as string
txtLName as string
EmpIDNo as number
dteHire as Date/Time
fkDeptID as Number
dteInactive as Date/Time


table tblEmpDaysOff with the following fields. This table contains multiple entries for each employee one for each day they are off:
pkEmpDaysOffID as AutoNumber
fkEmployeeID as number
longDayNumber as number


table tblDepartments with the following fields. This table contains the information regarding which department each employee is in:
pkDeptID as autonumber
txtDepartmentName as Text

 

[Galaxiom]

Join all the tables on the appropriate keys.
Include fields DeptID, DayNumber and EmployeeID
Turn on Totals.
Group By DeptID and DayNumber and Count on EmployeeID

This will produce a count of Employees by Dept and DayNumber.

Make another query with Employees and Department.
Group By Dept and Count Employees.

Combine the two queries and subtract the Counts fields.

 

[Coleman984]

I tried this query but I keep getting a syntax error message.

SELECT [tblEmployee].[txtFName],[ tblEmployee].[txtLName]
FROM [tblEmployee] left JOIN [tblDepartments] ON [tblDepartments.pkDeptID] = [tblEmployee.fkDeptID]
Left JOIN [tblEmpDaysOff]
ON [tblEmployee].[pkEmployeeID] =  [tblEmpDaysOff].[fkEmployeeID]
WHERE (((tblEmpDaysOff.longDayNumber) Is Null))
ORDER BY tblEmployee.txtFName;

 

[Coleman984]

Join all the tables on the appropriate keys.
Include fields DeptID, DayNumber and EmployeeID
Turn on Totals.
Group By DeptID and DayNumber and Count on EmployeeID

This will produce a count of Employees by Dept and DayNumber.

Make another query with Employees and Department.
Group By Dept and Count Employees.

Combine the two queries and subtract the Counts fields.

If i were well versed in making queries that might be something I could do. However I am not sure how to create said queries, would you know the proper sql syntax for that?

 

[Coleman984]

it should be pointed out that the days are stored as numbers. 1 = sunday and 7 = saturday.

How would that change what you've suggested?

 

[Coleman984]

I don't understand how these two are subtracted...since the first query doesn't give a sum of persons off...

 

[Coleman984]

Well I got it to work sort of, the only thing is it isn't showing days where no one is schedule off. How do I get it to show days even if no one is off?

 

[jzwp22]

If you want to show the number of employees available to be scheduled for each department for each day of the week you will need a series of queries. First, you need to associate each day of the week with each department. To do this you need a table that holds the day numbers of the week (tblDayNumbers). You need to put that table in a query with the department table. No joins between the tables should be made. This will give you the Cartesian Product of the two tables (7 day numbers for each department). I called this query qryDeptDays in the attached database.

Now create the query that Galaxiom suggested that counts all employees in each department. I called this qryNoOfEmpByDept in the attached.

Now create another query. Include both qryDeptDays and qryNoOfEmpByDept in the query and join by the deptID. I called this query qryDeptDayAllEmp. This query gives the total employees in each department for each day (assuming no days off for anyone--yet).

Now create the other query that Galaxiom discussed that counts the number of employees off on a day by dept. I called the query qryNoOfEmpOfByDayinDept

Now create another new query, this time include qryDeptDayAllEmp and qryNoOfEmpOfByDayinDept. You will need to make left joins between the two queries using both the deptID and longdaynumber fields. Include all the fields from qryDeptDayAllEmp. You will have to create an calculated field that uses the # of employees that are off field from qryNoOfEmpOfByDayinDept. If that count field is null return a zero, if it is not null return the count. Then finally, create another calculated field that finds the difference between the total employees in the dept and the number that are off (the number available for scheduling). This last query is called: qryAvailEmpToScheduleInDeptForEachDay

DB attached.

 

[Coleman984]

If you want to show the number of employees available to be scheduled for each department for each day of the week you will need a series of queries.
DB attached.

As always very helpful. Thanks I will review this in a bit and see if I can get it to work. Thanks much again jz.

 

[Coleman984]

You know I actually had 3 of the 4 parts of that correct. The last part that you just powered through is where I was hitting a brick wall. Thanks so much, now to read the sql code to try to make sense of it.