Solved

Attendance Report [Access]

Posted on 2010-08-22
4
357 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 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

718 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