?
Solved

Attendance Report [Access]

Posted on 2010-08-22
4
Medium Priority
?
358 Views
Last Modified: 2013-11-29
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.
0
Comment
Question by:Rahul Sehrawat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 33495898
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
 

Author Comment

by:Rahul Sehrawat
ID: 33495987
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
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 33496094
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
 

Author Comment

by:Rahul Sehrawat
ID: 33496235
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

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question