Attendance and Salary Register

Discuss the database features

Attendance and Salary Register

Postby amitmahajan74 » Thu Feb 23, 2017 8:35 am

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
Open Office 4.1.2
Win 10 Proff
amitmahajan74
 
Posts: 44
Joined: Mon Aug 27, 2012 12:23 pm

Re: Attendance and Salary Register

Postby chrisb » Thu Feb 23, 2017 9:24 pm

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   Expand viewCollapse view
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.
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 178
Joined: Mon Jun 07, 2010 4:16 pm

Re: Attendance and Salary Register

Postby eremmel » Thu Feb 23, 2017 9:48 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1035
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Attendance and Salary Register

Postby amitmahajan74 » Fri Feb 24, 2017 11:42 am

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

Amit
Open Office 4.1.2
Win 10 Proff
amitmahajan74
 
Posts: 44
Joined: Mon Aug 27, 2012 12:23 pm


Return to Base

Who is online

Users browsing this forum: Google [Bot] and 3 guests