Attendance and Salary Register

[amitmahajan74]

Hi,

I started by creating a Salary register for my employees. I created tables to input Employee name and salary, then attendance days and Over Time table to record any extra hours worked. In addition I also made a table with increment, if any employee gets a salary increment, it is input here.

All these have been connected to forms and I can enter employees, their attendance and over time record. Now I would want a single query to list all the employees, their attendance and Over Time hours and salary based on days worked.

To get the latest salary of an employee, I have used casewhen to find if the employee record is in increment table, then use that value otherwise value from employee table. I am stuck in this scenario, if an employee gets an increment twice, then how to frame casewhen to use the latest value. (The increment table has empID, NewSalary, Date). The casewhen is used as

casewhen( "Employee"."ID" = "WageIncrement"."EmpID", "WageIncrement"."NewWages", "Employee"."GrossSalary" ) AS "Salary"

Thanks,
Amit

[chrisb]

amitmahajan74,

i could show you how to solve this issue using your current set up but that would only encourage you to stick with what is a poorly designed database.
it's best to address your design issues before proceeding any further only to discover that your data has become unmanageable.

1. lets say that you have a table called "Employee" with the fields "ID" (auto value primary key), "LName", "FName", "StartDate", EmployeeNumber" etc.
2. you have a second table called "Salary" with the fields "ID" (auto value primary key), "Employee_ID" (foreign key references "Employee"."ID"), "Salary" & "Date"
then the code below could be used to display employees & their current salaries.

Code: Select all

select E."LName" || ' ' || E."FName" "Employee", S."Salary" "CurrentSalary"
from
"Employee" E
join
(	select "Employee_ID", "Salary"
	from "Salary" S
	where "Date" = (select max("Date") from "Salary" where "Employee_ID" = S."Employee_ID")
) S
on S."Employee_ID" = E.ID
order by "LName", "FName"

please bear in mind that i have not tested the code.

[eremmel]

amitmahajan74, as a business man, I can imagine that your time is valuable. So why not consider a open source software to manage your employees. Google on 'open source HRM software'. When you start with this kind of things you end up with lots of extra stuff over time: health care, faxes, insurances etc. I expect that there are nice products out there that match your needs. Learning to work with that software costs your time, but I assure you making it yourself will be more costly over time.

[amitmahajan74]

Thanks for the advise, will rework on the database design and will get back if I get stuck again.

Amit