CODEWITHSUNDEEP
sqlms-access
GabourX
GabourX

Reputation: 283

calculating month salary for an employee

I am working on my Database in MS Access 2010

and i Need to build a query to Calculate the month salary for each Employee

it goes like this :

  1. Input from user , which Year
  2. Input from user again , which Month
  3. Show Every Employee's Salary for the Input date

There are 2 Tables in the Query : Shifts , Employees

Shifts has a field for EmployeeID and a field for Day

Day field format is : Short Date

The problem is i don't know how to access the Month and the Year only !

I know that this is completely wrong , but i wanna do something like this:

        SELECT 
FROM EmployeesTBL INNER JOIN ShiftsTBL ON EmployeesTBL.EmployeeID = ShiftsTBL.EmployeeID
WHERE 
Year(ShiftsTBL.Day)=[Enter Year]
AND 
Month(ShiftsTBL.Day)=[Enter Month]
;

What do i need to write after SELECT to get the Sum of all Shifts and divide it by number of days the emp worked

Note : in the Shifts Table , i have EntryDate and ExitDate for every shift

Upvotes: 0

Views: 5271

Answers (1)

Leighbee
Leighbee

Reputation: 161

Access has a bunch of built in date functions. I believe Month(date) and Year(date) will give you what you need.

Something like 

SELECT EmpName
FROM Employees, Shifts
WHERE Employees.EmployeeID = Shifts.EmployeeID
AND
Month(Shifts.Day) = INPUT2.VALUE
AND
Year(Shifts.Day) = INPUT1.VALUE

should get you what you want!

EDIT: Aggregation: how this works will depend on how your database is set up. I think I understand you want to sum the hours worked and divide by the number of days?

If so, you will use Sum() and Count(). And you will Group By EmployeeID

SELECT Sum(Shifts)/Count(DaysWorked) AS SumDividedByCount
FROM EmployeesTBL INNER JOIN ShiftsTBL 
 ON EmployeesTBL.EmployeeID = ShiftsTBL.EmployeeID
WHERE 
  Year(ShiftsTBL.[Day])=[Enter Year]
AND 
   Month(ShiftsTBL.[Day])=[Enter Month]
GROUP BY EmployeeID

I used the WHERE clause because I think the results need to be filtered before they're grouped. If the results needed to be filtered after they were grouped, the HAVING clause would be used (and would go AFTER the GROUP BY)

Upvotes: 2

Related Questions