Solved

Attendance Report [Access]

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now