Please Help..

[arifmasum]

Hope everybody is fine in this forum.
I need help to print Attendance report for each employee.

In my attachment there are three tables. tblEmpAttendance Contails Attendance Records, tblEmpHoliday contains holiday Records and tblEmpLeave contains leave records.

I need to print monthly attendance report on every employee. I can not understand how should I do it.

Will somebody please help me?

Thanks in advance for valuable time and comments.

Arif Masum

 

[jal]

Your question seems too broad. I think you need to decide upon a strategy and then start attempting to bring it to completion, and then ask for help on any specific problems you are running into.

 

[arifmasum]

Thanks the main point

Your question seems too broad. I think you need to decide upon a strategy and then start attempting to bring it to completion, and then ask for help on any specific problems you are running into.

I want to update leave and holiday record in attendance records. Then I want to print the monthly attendance summery. I can do the monthly summery, i wonder how I will add the leave and holidays in attendance.

Thanks a lot for your answer and time.....

 

[jal]

I want to update leave and holiday record in attendance records. Then I want to print the monthly attendance summery. I can do the monthly summery, i wonder how I will add the leave and holidays in attendance.

Thanks a lot for your answer and time.....

Sorry, but I am not very smart. I still don't understand. You say, "I want to update holiday record." Update it with what new info? To "update" a table means to use new information to modify the records of an existing table or add new info to the table. Do you have a new list of new holidays to add to the table? Do you have a complete list of holidays for the whole year (I didn't see any such list in your file).

At this point I can't even figure out if you're having a query problem, on the one hand, or a print-layout problem on the other. If the problem is you can't figure out how to make Access arrange the info on a printed document, I can't help you because I do all my printing in VB.NEt, not in Access and VBA.

If the problem is you don't know how to set up a query (such as an Update query), maybe I can help, if it is a fairly simple query (I am just a beginner), but in this case you should show a sample attempt at the query so perhaps we can figure out why your query is failing.

I'm sorry, but I'm getting tired of trying to figure out where the problem is.

 

[arifmasum]

The Clear Point

Thanks a lot for your reply.. I am too sorry for your inconvenience...

First, I have attendance data in tblEmpAttendance, by that I can view any employee's attendance record. For holidys tblEmpHoliday contains holiday information. In tblEmpLeave there is record for employee leave.. We wouldn't find attendance record in tblEmpAttendance. So, my problem is, i want to add the leave records in tblEmpAttendance and remark them as leave for the specific employee. I wonder how should i design the query.

An employee can be in leave for 5 days, I need to mark those 5 days as leave in attendance table. I hope you will get clear view of my problem now.

Thanks a lot for your time and help.
Best regards.

Arif Masum

 

[jal]

Ok, I have some ideas on how to get started. I am not good enough to know the "professional" way to do this, but I might help you get the job done some kind of way. Give me a few minutes on this and I'll come back with a suggestion.

 

[arifmasum]

Thanks ....

Thank you so very much... I will be waiting for your suggession.

Best regards.

Arif Masum

 

[jal]

Thank you so very much... I will be waiting for your suggession.

Best regards.

Arif Masum

Sorry, I am not making much progress because there were some basics unknown to me - I didn't know how to cross product two tables !!!.

Anyway, I figured a starting point would be to use a cross product to build a table containing the 365 days of the year (see attached). To do this, I just put numbers 1 to 366 in a table and then crossed that table with a Years table (currently has only 2008) and I call the resulting table "the Calendar".


My thinking is this. Suppose the date range is from 04/26/2008 to 05/26/2008.

You can use the Calendar to get a list of all the dates in that range and then, one by one, check each date to see if it is a holiday or a date of leave (I don't know if they work Sundays by the way). If not a holiday or date of leave or a Sunday, then you can regard that date as absentee.

So far all I've got done is the Calendar - here's the Cross Product query:

Select DateAdd("D", D.DayNo, CDate("01/01/" + Y.YearNo))
INTO Calendar
FRom
DaysAs1To366 as D, YearNOs as Y

 

[jal]

Oh, I should have added the column name DayOfCalendar

Select DateAdd("D", D.DayNo, CDate("01/01/" + Y.YearNo)) as DayOfCalendar
INTO Calendar
FRom
DaysAs1To366 as D, YearNOs as Y

 

[jal]

Ok, a little progress. Using two more queries, it results in a table of 95 entries because there were 5 employees, each worked over 19 day timespan. For each emp, this table contains the 19 dates in his span.


*************************
Next, we'll get the start date and end date for each employee:

SELECT IDNo, MIN(Date) AS FirstDateWorked, MAX(DAte) AS LastDateWorked, DateDiff("D", FirstDateWorked, LastDateWorked) AS DateSpan
INTO StartAndEndDAtes
FROM
tblEmpAttendance
GROUP BY IDNo

******************************************
Now we'll cross product the employees with the Calendar to get a list of all the dates in that range for each employee



SELECT * INTO DateSpanForEachEmp FROM
(
SELECT DISTINCT Emp.IDNo, C.DAyOfCalendar FROM tblEmpAttendance AS Emp, CAlendar AS C
) AS AllPossibleDAys
INNER JOIN StartAndEndDates AS S ON S.IDNo = AllPossibledays.IdNo

WHERE S.FirstDAteWorked <= AllPossibleDays.DayOfCalendar AND AllPossibleDays.DayOfCalendar <= s.LastDAteWorked



The table DateSpanForEachEmp now contains the 95 entries (19 dates for each employee). For each emp, you can now run queries against this table to see if he was absent from work on any of those 19 dates, or if any of those 19 days was a holiday, or if any of those 19 days was a day on leave. (YOu may also have to check whether any of the 19 days was a Sunday as off-day). This should get you started in the right direction, now.


No?

 

[arifmasum]

Please have a look

Thank you so very much... Calendar table is a new way to add records in my database. I need to design my database to print reports in the format of attached image. will you give a look and let me know how I can manipulate the calendar table to get the report?

Thanks and best regards

Arif Masum

 

[jal]

Thank you so very much... Calendar table is a new way to add records in my database. I need to design my database to print reports in the format of attached image. will you give a look and let me know how I can manipulate the calendar table to get the report?

Thanks and best regards

Arif Masum

The Calendar table was just a stepping stone. I think you should work with DateSpanForEachEmp table (see my last post).

First of all, modify the query that forms that table. This time, add an Order By Clause. Here's the new version.

Select * INTO DateSpanForEachEmp
FROM
(
SELECT * FROM
(
SELECT DISTINCT Emp.IDNo, C.DAyOfCalendar FROM tblEmpAttendance AS Emp, CAlendar AS C
) AS AllPossibleDAys
INNER JOIN StartAndEndDates AS S ON S.IDNo = AllPossibledays.IdNo
WHERE S.FirstDAteWorked <= AllPossibleDays.DayOfCalendar AND AllPossibleDays.DayOfCalendar <= s.LastDAteWorked
)
Order By AllPossibleDays.IDNo, AllPossibleDays.DayOfCalendar


This table can be your report source if you add some columns. First of all, add ALL the attendance table columns by inner joining to that table. But also add another column called DateStatus whose values will be such like "Holiday", "Worked", "Leave". (initially it it will be "Undetermined". In other words, do this:

SELECT "Undetermined" AS DateStatus, D.*, Emp.*
INTO ReportSource
FROM DAteSpanForEAchEmp AS D
LEFT JOIN tblEmpAttendance AS Emp ON Emp.IDNo = D.S_IDNo AND Emp.Date = D.DayOfCalendar


Then run UPdate queries on ReportSource as to set the value for the new DateStatus column as "Holiday", "Worked", "Leave", etc. Once you have done that, you should be able to put any desired columns of ReportSource into your report.

 

[arifmasum]

Thank you so very much... I am greatful to you dear friend.... I called you friend coz who stays in hard time is a friend... I am working on it.... My office is close for today... Tomorrow I will write more for development of all forum members... I like this forum very much... It gives me a lot... I wish to give back when I will be able to help other members....

Best regards

Arif Masum

 

[khawar]

Check this out

Hi Arif

check if this one works

first of all i extended the leaves table to show all days in the query step 1
then combined it with attendance table

go to queries and check it step by step and let me know if it is like what you want


Regards

Khawar

 

[jal]

Hi Arif

check if this one works

first of all i extended the leaves table to show all days in the query step 1
then combined it with attendance table

go to queries and check it step by step and let me know if it is like what you want


Regards

Khawar

Thanks khawar - looks very brilliant. You seem to be doing a much than better job than I.

 

[arifmasum]

Thanks a lot...

Brilliant solve of the issue...
Thanks khawar. You seem to be doing much better job ... Thats why I feel this forum is best....
That's what I was in need.
Hope I will be able to complete the rest of the process.. I will let you peoples know if i am stuck again...

Thanks with best regards to all of you.

Arif Masum