Attendance Report [Access]

Hey,
I recently joined a company and they gave me to work on some old attendance database system.

There are two tables:

Table 1: Staff Details
 - Name
 - Rate_Per_Hour

Table 2: Staff Attendance
 - Name
 - Date
 - Time_IN
 - Time_OUT

Now I want a query which will ask the user to ask the From_Date and To_Date, and the query will calculate the (Total Hour as Time_OUT-Time_IN) * Rate_Per_Hour * (No_of_Days as To_Date-From_Date) with Distinct Names

So it will be displayed as:
Name | Rate_Per_Hour | No_of_Day | Total Income ( Rate_Per_Hour * No_of_Day)

Please help.
Rahul SehrawatAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
Here's a change to parameterize the date:

Select s.Name, s.Rate_Per_Hour, count(*) as No_of_day,
sum(datediff("n",a.Time_in,a.Time_out))/60 * s.rate_per_hour  as Total_Income
From
tblStaff as S inner join tblAttendance as A
on s.Name = a.Name
where a.date between [Enter First Date]  and [Enter Last Date]
group by s.name, s.rate_per_hour


For number of days, the query returns number of days worked.  If you want number of calendar days, then change "count(*)" to "datediff("d", min(a.date), max(a.date))

Your total income calculation does not make sense.  I will gladly sign a 1 year contract at $1 per hour:

number of days * number of hours * rate per hours
 
260 *  2080  *  $1  =   $540,000  

0
 
dqmqCommented:
I'll assume that Name is a unique key and Name + Date is a unique key and that you pay for partial hours worked.  

Select s.Name, s.Rate_Per_Hour, count(*) as No_of_day,
sum(datediff("n",a.Time_in,a.Time_out))/60 * s.rate_per_hour  as Total_Income
From
tblStaff as S inner join tblAttendance as A
on s.Name = a.Name
group by s.name, s.rate_per_hour
0
 
Rahul SehrawatAuthor Commented:
Hey,

Thanks for the answer. I suppose there was bit of mistake in explaining the question. It almost fine. But the thing is
It's asking for Time_in and Time_out, what I actually wanted is
It should ask for From_date and To_Date from User
so the system will calculate the "No_of_Days" through the Difference of From_date and To_Date

And It will calculate:
No_of_Days * (No_of_Hours spent between these Days) * rate_per_hour

Sorry for the inconvenience. :(
0
 
Rahul SehrawatAuthor Commented:
Actually my company does not make sense, they change their requirement every hour.. :(
lol.. btw, problem solved.
They changed their requirement and It's done.

Your answer really helped.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.