Solved

Attendance Report [Access]

Posted on 2010-08-22
4
356 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
  • 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.ā€‹
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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ā€¦

726 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