Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-18-2019, 12:53 PM   #1
indrajeetpramalik
Newly Registered User
 
Join Date: Aug 2019
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
indrajeetpramalik is on a distinguished road
Leave List as per Date Range

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

LeaveAppliedOn........LeaveAppliedTill........Empl oyeeName
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.
Attached Files
File Type: mdb LeaveList.mdb (112.0 KB, 5 views)


Last edited by indrajeetpramalik; 08-18-2019 at 01:35 PM.
indrajeetpramalik is offline   Reply With Quote
Old 08-18-2019, 01:03 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,311
Thanks: 537
Thanked 939 Times in 890 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Leave List as per Date Range

The results don't match the data supplied? Please explain or correct it...
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-18-2019, 01:08 PM   #3
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 999
Thanks: 71
Thanked 49 Times in 42 Posts
MickJav will become famous soon enough
Re: Leave List as per Date Range

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

__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
Old 08-18-2019, 01:15 PM   #4
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,311
Thanks: 537
Thanked 939 Times in 890 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Leave List as per Date Range

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!"
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-18-2019, 01:16 PM   #5
indrajeetpramalik
Newly Registered User
 
Join Date: Aug 2019
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
indrajeetpramalik is on a distinguished road
Re: Leave List as per Date Range

some data missed on previous post.. here it is again:


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

LeaveAppliedOn........LeaveAppliedTill........Empl oyeeName
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.
indrajeetpramalik is offline   Reply With Quote
Old 08-18-2019, 01:19 PM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,232
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Leave List as per Date Range

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-18-2019, 01:27 PM   #7
indrajeetpramalik
Newly Registered User
 
Join Date: Aug 2019
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
indrajeetpramalik is on a distinguished road
Re: Leave List as per Date Range

Quote:
Originally Posted by jdraw View Post
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
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 by indrajeetpramalik; 08-18-2019 at 01:36 PM.
indrajeetpramalik is offline   Reply With Quote
Old 08-18-2019, 02:17 PM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Leave List as per Date Range

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 08-18-2019 at 04:04 PM.
June7 is offline   Reply With Quote
Old 08-18-2019, 04:00 PM   #9
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,311
Thanks: 537
Thanked 939 Times in 890 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Leave List as per Date Range

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.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-18-2019, 05:35 PM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,232
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Leave List as per Date Range

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...d.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.
Attached Files
File Type: mdb LeaveList.mdb (304.0 KB, 3 views)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 08-18-2019 at 06:37 PM.
jdraw is offline   Reply With Quote
Old 08-18-2019, 06:31 PM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,557
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Leave List as per Date Range

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-18-2019, 09:54 PM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Leave List as per Date Range

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];
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-18-2019, 11:00 PM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,988
Thanks: 114
Thanked 3,007 Times in 2,734 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Leave List as per Date Range

@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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] List dates between date range lookforsmt Modules & VBA 16 06-08-2019 04:48 AM
Convert Date Range to Date List IAmTodd Queries 2 09-10-2010 07:28 AM
It is possible to list all dates inbetween a date range? Please help joe789 Queries 3 08-20-2004 11:45 AM
date range fields leave blank for all tracey75 Forms 9 06-12-2001 06:25 PM




All times are GMT -8. The time now is 09:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World