March 24, 2006 at 9:45 am
Beolw is the tables Structure and dummy data used in Access database
Employee
========
EmpId(Pk int) EName(Text) DeptId(Fk int)
1 Rashid 1
2 Kashif 2
3 Black 1
4 White 2
5 Brown 2
Department
==========
DeptId(Pk int) DName(Text)
1 Labor
2 Secret
3 Production
4 Purchase
Attendance
==========
AttId(Pk Int) EmpId(Fk int) In/Out_Time(Datetime) Direction
1 2 2/5/2006 6:37:02 PM 1 // 1 for IN, 0 For OUT
2 2 2/5/2006 8:37:02 PM 0
3 2 2/5/2006 8:50:00 PM 1
4 1 2/5/2006 8:51:00 PM 1
5 1 2/5/2006 9:37:02 PM 0
6 2 2/5/2006 9:40:02 PM 0
7 3 2/5/2006 6:37:04 PM 1
8 4 2/5/2006 6:37:06 PM 1
9 5 2/5/2006 6:37:08 PM 1
10 5 2/5/2006 8:40:02 PM 0
11 4 2/5/2006 8:40:40 PM 0
12 3 2/5/2006 8:40:50 PM 0
13 2 3/5/2006 6:37:02 PM 1
14 2 3/5/2006 8:37:02 PM 0
15 2 3/5/2006 8:50:00 PM 1
16 1 3/5/2006 8:51:00 PM 1
17 1 3/5/2006 9:37:02 PM 0
18 2 3/5/2006 9:40:02 PM 0
19 3 3/5/2006 6:37:04 PM 1
20 4 3/5/2006 6:37:06 PM 1
21 5 3/5/2006 6:37:08 PM 1
22 5 3/5/2006 8:40:02 PM 0
23 4 3/5/2006 8:40:40 PM 0
24 3 3/5/2006 8:40:50 PM 0
And so on the attendance data for whole month.
What i need is an optimized efficient query that takes a Start_date(DateTime) as an input parameter and returns me the
Attendance data of all Employees for one week. Means starting from startdate and ending at startdate+6 days in the following
format
EName DName IstDay 2ndDay 3rdDay 4thDay 5thDay 6thDay 7thDay
Where as IstDay to 7thDay Columns contains the value for total Working hrs at that day as
IstDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time) for any EMpId]
2ndDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+1 Day) for any EMpId]
3rdDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+2 Days) for any EMpId]
4thDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+3 days) for any EMpId]
And so on upto 7th day.
Now i m in need of an efficient query that returns me the required above result set.
NOTE
====
I am using Access 2000 database so i need a query or anything else that may give me the required output in Access database
Thnx in Advance
March 27, 2006 at 8:00 am
This was removed by the editor as SPAM
March 30, 2006 at 9:44 am
IMO, you're not likely to get much back against this post. You've put your data and a requirement up, and basically asked the community to do your work for you.
You'll do better at getting reponses, I believe, if you'll go ahead and tackle the problem and then post a specific question when you get stuck.
You may want to consider some of these as you start:
format(mydatefield, "ww") (in case you want actual calendar weeks)
grouping/summing queries
crosstab queries
parameterized queries
As an example of a parameterized query, you can write sql like this:
select mydatefield, field1, field2... from mytable
where mydatefield between [mystartdate] and ([mystartdate] + 6).
If you run that in access with real table and field names, access will prompt you for mystartdate once, and then run.
I'd say put a little more effort into your task and come back when you have a more focussed question.
April 4, 2006 at 9:01 am
Click here to download an Access database that shows you a suggested solution to your question. Review Query4, which is a Crosstab query to show the number of minutes worked by employee by date. But you also need to review Query3 as it shows that there is an error if any employee has multiple In/Out s in one day. There will also be a problem if an employee comes in before midnight, and leaves after midnight. The only way around these two situations would be to write VBA code to pair an employee's In and Out records, and assign the correct date to the minutes worked for that pair of In and Out records.
Hope this helps
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
April 4, 2006 at 12:11 pm
A Bundle Of Thnks Rauch! For your nice and Compact solution. Thnx once again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply