Solved Leave List as per Date Range

I

indrajeetpramalik

New member
Local time
Today, 12:56
Joined
Aug 14, 2019
Messages
15
I have a Table "Leave" containing data as below:

LeaveAppliedOn........LeaveAppliedTill........EmployeeName
03-Jul-19.................06-Jul-19.................A
04-Jul-19.................05-Jul-19.................B
05-Jul-19.................08-Jul-19.................C
10-Jul-19.................14-Jul-19.................A
10-Jul-19.................14-Jul-19.................D
14-Jul-19.................18-Jul-19.................E
15-Jul-19.................15-Jul-19.................B
20-Jul-19.................20-Jul-19.................F

LeaveAppliedOn and LeaveAppliedTill is DateRange of Leave given to EmployeeName

Now i want to have result in query from these data as below:

LeaveDate........EmployeeName
3-Jul-19...........A
4-Jul-19...........A
5-Jul-19...........A
6-Jul-19...........A
4-Jul-19...........B
5-Jul-19...........B
5-Jul-19...........C
6-Jul-19...........C
7-Jul-19...........C
8-Jul-19...........C
10-Jul-19.........A
11-Jul-19.........A
12-Jul-19.........A
13-Jul-19.........A
14-Jul-19.........A
10-Jul-19.........D
11-Jul-19.........D
12-Jul-19.........D
13-Jul-19.........D
14-Jul-19.........D
14-Jul-19.........E
15-Jul-19.........E
16-Jul-19.........E
17-Jul-19.........E
18-Jul-19.........E
15-Jul-19.........B
20-Jul-19.........F

Sample Database with records is attached. Help Appreciated. Thanks.
 

Attachments

Last edited:
The results don't match the data supplied? Please explain or correct it...
 
I'm just about to go but from the looks of it you'll need to code a sulution as from the looks of it you have a start date and end date but you also have all the dates inbetween those two.


off the top of my head 2 loops should do it the first looping though the table the second getting all the dates something like for date1 to date2.


sorry can't be of more help hopefully somebody else will be able to point you in the right direction



mick
 
I answered a similar question a while back, it's on my website here:-

Create Many Records

The download file is available for free just use the coupon code:- "BuyMeA_Coffee!"
 
some data missed on previous post.. here it is again:


I have a Table "Leave" containing data as below:

LeaveAppliedOn........LeaveAppliedTill........EmployeeName
03-Jul-19.................06-Jul-19.................A
04-Jul-19.................05-Jul-19.................B
05-Jul-19.................08-Jul-19.................C
10-Jul-19.................14-Jul-19.................A
10-Jul-19.................14-Jul-19.................D
14-Jul-19.................18-Jul-19.................E
15-Jul-19.................15-Jul-19.................B
20-Jul-19.................20-Jul-19.................F

Now i want to have result in query from these data as below:

LeaveDate........EmployeeName

3-Jul-19...........A
4-Jul-19...........A
5-Jul-19...........A
6-Jul-19...........A
4-Jul-19...........B
5-Jul-19...........B
5-Jul-19...........C
6-Jul-19...........C
7-Jul-19...........C
8-Jul-19...........C
10-Jul-19.........A
11-Jul-19.........A
12-Jul-19.........A
13-Jul-19.........A
14-Jul-19.........A
10-Jul-19.........D
11-Jul-19.........D
12-Jul-19.........D
13-Jul-19.........D
14-Jul-19.........D
14-Jul-19.........E
15-Jul-19.........E
16-Jul-19.........E
17-Jul-19.........E
18-Jul-19.........E
15-Jul-19.........B
20-Jul-19.........F

Sample Database with records is attached. Help Appreciated. Thanks.
 
Try this (as Tony said results don't match the data supplied)
Code: 
SELECT Leave.LeaveAppliedOn
, Leave.LeaveAppliedTill
, Leave.EmployeeName
FROM Leave
ORDER BY Leave.LeaveAppliedOn;


Please define LeaveDate
 
jdraw said:
Try this (as Tony said results don't match the data supplied)
Code: 
SELECT Leave.LeaveAppliedOn
, Leave.LeaveAppliedTill
, Leave.EmployeeName
FROM Leave
ORDER BY Leave.LeaveAppliedOn;


Please define LeaveDate
Click to expand...

LeaveAppliedOn and LeaveAppliedTill is DateRange of Leave given to EmployeeName in Table "Leave"

LeaveDate and EmployeeName is to be Generated in query From data in "Leave" Table.. Like:

LeaveDate........EmployeeName
3-Jul-19...........A
4-Jul-19...........A
5-Jul-19...........A
6-Jul-19...........A
4-Jul-19...........B
5-Jul-19...........B
5-Jul-19...........C
6-Jul-19...........C
7-Jul-19...........C
8-Jul-19...........C
10-Jul-19.........A
11-Jul-19.........A
12-Jul-19.........A
13-Jul-19.........A
14-Jul-19.........A
10-Jul-19.........D
11-Jul-19.........D
12-Jul-19.........D
13-Jul-19.........D
14-Jul-19.........D
14-Jul-19.........E
15-Jul-19.........E
16-Jul-19.........E
17-Jul-19.........E
18-Jul-19.........E
15-Jul-19.........B
20-Jul-19.........F
 
Last edited:
Don't see any difference in data samples. Desired results do seem to match the raw data input.

This is a fairly common topic: creating records from a range of values. It can be done by query but will likely run slowly with a large dataset - it's actually slow with even a small dataset. Consider this example you can copy/paste into query SQLView:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT DISTINCT Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "mm/dd/yyyy") AS MDY, Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "yyyy q") AS YearQtr, 1 AS Data
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE [StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 Between [StartDate]-1 And [EndDate];

Now having the begin and end dates fed in from a table to return a sequence for each employee is the tricky part. I will have to explore some more because I can't find examples I've seen before. However, I expect a VBA solution might perform faster.

Looking at Uncle Gizmo's file now. Download process is a little complicated. Yes, VBA and 'temp' table approach is faster.

Might find this of interest https://www.accessforums.net/showthread.php?t=18459. It has sample db that runs similar process.
 
Last edited:
indrajeetpramalik

I note that you have re-posted this question practically as is, in other words exactly the same as the question you posted here. Now this might have been a mistake, but if you did it because you're not happy with the response you are getting in this thread, then that's not the way to go about it. I have assumed that it was a mistake and have deleted the duplicate. Your best approach is to respond to the the questions posed by the people trying to help you here, if there's anything you don't understand, then explain.
 
Quite often you see Leave Requests along these lines:

LeaveStartDate
LeaveDurationDays
LeaveType (if you have things like education, bereavement, legal, vacation, sabbatical...)

Update:
I am attaching an updated version of your database.
I did not use a query directly. I saw a function on AWF by lookforsmt called MakeDates. I adapted that function to deal with your Leave table.
I also created:
- another table tblEmployeeLeaveDates
- a query called Q_OnLeaveDatesByEmployee
- a subroutine called DoLeave that uses the modified Makedates function
- and a form frmMain.

You click the button on form main. Any data existing in tblEmployeeLeaveDates is deleted. The Leave table is processed by the doLeave procedure which calls the MakeDates function for each record in your Leave table. This populates tblEmployeeLeaveDates with the data you requested. Then query Q_OnLeaveDatesByEmployee displays the info.
The makedates function at https://www.access-programmers.co.uk/forums/showthread.php?t=305359 was very useful in setting up this approach.

Code: 
' ----------------------------------------------------------------
' Procedure Name: MakeDates
' Purpose:To create a table of dates where a specific employee is OnLeave
'This is based on a function from
'https://www.access-programmers.co.uk/forums/showthread.php?t=305359
'Adapted to hndle Employee
' Procedure Kind: Function
' Procedure Access: Public
' Parameter dtStart (Date):
' Parameter dtEnd (Date):
' Parameter Employee (String):
' Return Type: Long
' Author: from AWF lookforsmt
' Date: 18-Aug-19
' ----------------------------------------------------------------
Function MakeDates(dtStart As Date, dtEnd As Date, Employee As String) As Long
10        On Error GoTo MakeDates_Error
          Dim dt As Date
          Dim rs As DAO.Recordset

20        Set rs = DBEngine(0)(0).OpenRecordset("tblEmployeeLeaveDates")
30        With rs
40            For dt = dtStart To dtEnd
50                .AddNew
60                !LeaveDate = dt
70                !Employee = Employee
80                .Update
90            Next
100       End With
110       rs.Close
120       Set rs = Nothing

          
130       On Error GoTo 0
140       Exit Function

MakeDates_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MakeDates, line " & Erl & "."

End Function
Code: 
' ----------------------------------------------------------------
' Procedure Name: doLeave
' Purpose: Routine to populate a table with all dates an Employee is onLeave
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 18-Aug-19
' ----------------------------------------------------------------
Sub doLeave()
10        On Error GoTo doLeave_Error
          Dim rs As DAO.Recordset
          Dim db As DAO.Database
20        Set db = CurrentDb
30        Set rs = db.OpenRecordset("Leave")
          'remove existing records from tblEmployeeLeaveDates
40        db.Execute "delete * from tblEmployeeLeaveDates", dbFailOnError
50        Do While Not rs.EOF
60            Call MakeDates(rs!LeaveAppliedOn, rs!LeaveAppliedTill, rs!EmployeeName)
70            rs.MoveNext
80        Loop
90     DoCmd.OpenQuery "Q_OnLeaveDatesByEmployee"
100       On Error GoTo 0
110       Exit Sub

doLeave_Error:

120       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure doLeave, line " & Erl & "."

End Sub

Good luck.
 

Attachments

Last edited:
The way I would approach this is to invert the problem.

Start with a list of work days or make a loop that iterates from one day to the next within a range.

Then for each day within that loop, find all persons whose starting leave date is less than or equal to today and whose ending leave is greater than or equal to today.

This becomes a series of queries that might be appropriate to place in a "working table" for display purposes. You would erase the table, then fill it from the aforementioned loop, display it, and the next time you need this, you repeat the process: erase, loop and fill, and display again as often as needed, changing only the start and stop dates.
 
create a query and name it qryNumbersFromMsysObjects:
Code: 
SELECT 
    DISTINCT 
        Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS;
now create the "final" query using the above query and your table (tblLeave):
Code: 
SELECT T4.mSysDates AS LeaveDate, tblLeave.EmployeeName
FROM tblLeave, (SELECT 
     DateSerial(Year(Date()),1,1+T1.NUMS+(T2.NUMS*10)+(T3.NUMS*100)) AS mSysDates 
FROM 
     qryNumbersFromMsysObjects AS T1, 
     qryNumbersFromMsysObjects AS T2,
     qryNumbersFromMsysObjects AS T3)  AS T4
WHERE (((T4.mSysDates) Between [tblLeave].[LeaveAppliedOn] And [tblLeave].[LeaveAppliedTill]))
the result is sorted by "LeaveDate" Ascending.
Code: 
result:
LeaveDate	EmployeeName
03-Jul-19	A
04-Jul-19	A
04-Jul-19	B
05-Jul-19	A
05-Jul-19	B
05-Jul-19	C
06-Jul-19	A
06-Jul-19	C
07-Jul-19	C
08-Jul-19	C
10-Jul-19	A
10-Jul-19	D
11-Jul-19	A
11-Jul-19	D
12-Jul-19	A
12-Jul-19	D
13-Jul-19	A
13-Jul-19	D
14-Jul-19	A
14-Jul-19	D
14-Jul-19	E
15-Jul-19	E
15-Jul-19	B
16-Jul-19	E
17-Jul-19	E
18-Jul-19	E
20-Jul-19	F

if you want to have a sort order as what you posted, add Autonumber field (ID) on tblLeave:
Code: 
SELECT T4.mSysDates AS LeaveDate, tblLeave.EmployeeName
FROM tblLeave, (SELECT 
     DateSerial(Year(Date()),1,1+T1.NUMS+(T2.NUMS*10)+(T3.NUMS*100)) AS mSysDates 
FROM 
     qryNumbersFromMsysObjects AS T1, 
     qryNumbersFromMsysObjects AS T2,
     qryNumbersFromMsysObjects AS T3)  AS T4
WHERE (((T4.mSysDates) Between [tblLeave].[LeaveAppliedOn] And [tblLeave].[LeaveAppliedTill])) 
ORDER BY [tblLeave].[ID];
 
@indra....
You now have two effective solutions by jdraw (based on a function) and arnelgp (using 2 queries) as well as a similar suggested approach by Doc.

I've tested both solutions. They are both fast and work equally well.
Choose one solution and use it.
 
You must log in or register to reply here.

Similar threads

H
Solved Finding names from table of not duplicated rows
Replies
11
Views
287
Pat Hartman
P
N
Add RowNumber Problem
Replies
11
Views
338
CJ_London
C
K
Solved How to solve this Top 1 Query?
Replies
6
Views
275
GaP42
G
MattBaldry
Start and End Times with Overlap
Replies
11
Views
218
arnelgp
arnelgp
S
Anyone can help on this ? Total Date In Access
Replies
2
Views
310
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom