Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2019, 05:18 AM   #1
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 242
Thanks: 102
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Summing date ranges

Hi there,
I haven't started the database yet but i'm wondering if it's possible to add date ranges together to get a total number of days. For example : If a person worked on a particular task from 1/1/19 to 6/1/19 and again on 2/3/19 to 7/3/19 and then again from 4/6/19 to 13/6/19....would it be possible to design a query to work out the total number of days the person spent on that task ?
I wasn't sure if this could be done with a query so i put it in this section.

Ps...the table would contain a StartDate and EndDate field to reflect the start and finish dates on each occasion

Thanks (Dates in UK format)

Emma35 is offline   Reply With Quote
Old 07-12-2019, 05:42 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,050
Thanks: 115
Thanked 3,020 Times in 2,747 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: Summing date ranges

Yes. The number of days in each date range would be individually calculated then summed.
You might find it easier to create a function to do this. If so, you will need to convert dates to US format in the function code
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Emma35 (07-21-2019)
Old 07-12-2019, 05:42 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,946
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Summing date ranges

Hi. It should be possible but may have to generate a row for each day worked to count it, which is also possible using just a query.

__________________
Just my 2 cents...

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 theDBguy; 07-17-2019 at 11:16 AM.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Emma35 (07-21-2019)
Old 07-12-2019, 05:44 AM   #4
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,405
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Summing date ranges

Yes, this will actually be fairly simple with the DateDiff function (https://www.techonthenet.com/access/...e/datediff.php).

In the query you will create a claculated field using DateDiff on the start and end dates, then make it an aggregate query and sum that calculated field.
plog is offline   Reply With Quote
Old 07-12-2019, 05:49 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 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: Summing date ranges

Emma,

You may find some insight in the Similar Threads identified at the bottom of this page.
__________________

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 online now   Reply With Quote
Old 07-12-2019, 10:46 AM   #6
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Summing date ranges

An alternate to Datediff is to subtract the two dates. But but be aware for someone worked July 6, 7 and 8 (3 days) that


DateDiff("d", #July 6 2019#, #July 8 2019#)
as well as

#July 8 2019# - #July 6 2019#
both give 2
Cronk is offline   Reply With Quote
Old 07-17-2019, 10:31 AM   #7
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 242
Thanks: 102
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Re: Summing date ranges

Thanks for the suggestions guys.....still not really sure how to go about this though. I've used the DateDiff function before but just for two dates and not to add time periods together. What i'm looking to do is to have a form where a user enters two dates and the query will return a list of people (highest to lowest) who have spent the most time on that task.
Apologies for taking a while to repost but I've a family member in hospital and it's a little difficult

Thank you

Emma35 is offline   Reply With Quote
Old 07-17-2019, 01:53 PM   #8
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,405
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Summing date ranges

Now you've significantly moved the goalposts, this isn't what you listed initially. Your first post just wanted to calculate total days over multiple rows without criteria. Now you want to apply criteria to individual rows, sum them and then find the person with the highest total.

For this you are going to need 2 queries, subQuery and Query. subQuery will be based on your table and use a DateDiff function to calculate total days wihtin the input timeframe. Query with sum those values and return the results you want.

Because of your added complexity I suggest you create a function in a module to determine the correct amount of worked days within the timeframe. It would take the timeframe dates as well as the Start/End dates from your tables and return the total days worked that fall within the timeframes.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Emma35 (07-21-2019)
Old 07-17-2019, 02:23 PM   #9
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 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: Summing date ranges

You'll have to ensure you are adding work hours/days. Seems you would ignore weekends and holidays, but you know the requirement better than readers.
__________________

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 online now   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
Emma35 (07-21-2019)
Old 07-17-2019, 03:40 PM   #10
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,047
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Summing date ranges

One thing to be very careful about; Periods that start BEFORE or end AFTER your date range.

John work from July 21st to September 7th. How do you want to reflect his time if your asking for only the month of August.

You will want to look at all end dates AFTER your start date and all start dates PRIOR to your end date. As such you may have to have an additional query that simply gives you your data (with start/end adjusted within the query based on passed start/end) to drive everything FIRST.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Emma35 (07-21-2019)
Old 07-18-2019, 06:37 AM   #11
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 242
Thanks: 102
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Re: Summing date ranges

Ok sorry if i've confused things a little. I've just had a meeting with the guy who needs the database and it's a bit clearer now what has to be done. I'll post a basic sample database in the morning (just a table with some fields and info) and outline what i'd like to do exactly and hopefully someone can up with a query to extract the information i need.
Thanks again for your time with this
Emma35 is offline   Reply With Quote
Old 07-18-2019, 11:55 PM   #12
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 242
Thanks: 102
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Re: Summing date ranges

Ok i've created a table and a query where i can work out how many days each person has spent on a task but how do i sum them together ? I'd like to add each persons days together to come up with a total for that person.
I've been told that the User would like to select a persons name from a combo box on a form and view all their StartDates and EndDates and also their total time spent on a task

Thanks all for your help
Attached Files
File Type: zip Test2.zip (21.0 KB, 7 views)
Emma35 is offline   Reply With Quote
Old 07-19-2019, 01:23 PM   #13
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Summing date ranges

Drop TaskID and Start/Enddates from the query. Group on EmployeeID (not sum) and sum the DaysWorked, your query will show the aggregate for each employee.


But if you read my post earlier, you will see that for accuracy you need
Code:
DaysWorked: [EndDate]-[StartDate] + 1
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
Emma35 (07-21-2019)
Old 07-19-2019, 01:42 PM   #14
KlausObd
Newly Registered User
 
Join Date: Jul 2019
Posts: 2
Thanks: 0
Thanked 1 Time in 1 Post
KlausObd is on a distinguished road
Re: Summing date ranges

Hi,
Dates: a) The datetime format in MSAccess always stores a point in time - internally stored as type double (the values before the . are the number of days since the 30.12.1899 (Bill Gates GrandFathers birthday) and the numbers after the . are the part of the day. 0 = midnight, date only no time 0.5 = High Noon etc. And the dates are only DISPLAYED as dates.
b) i for myself once did a day-table, with one record for each day between 2000 and 2100. It also contains the German holidays and the info if weekday or not etc. Extremely useful for date calculations as you easily can create a cross join between a start and an end date and that day table, containing all the dates in between, or for weekday calculation etc. Even if the table contains several records, it normally just sits in the corner - normally no updates are done on that ... I always use it, if i calculate days ...
mfg Klaus
KlausObd is offline   Reply With Quote
The Following User Says Thank You to KlausObd For This Useful Post:
Emma35 (07-21-2019)
Old 07-21-2019, 07:28 AM   #15
Emma35
Newly Registered User
 
Join Date: Sep 2012
Location: Ireland
Posts: 242
Thanks: 102
Thanked 0 Times in 0 Posts
Emma35 is on a distinguished road
Re: Summing date ranges

Great it's working now...thanks to everyone for your your advice.

Cronk i have one more quick question.....How does taking out the date fields make the query work ?.....and will i have to create a second query if i want to see all the StartDates and EndDates for each person ?

Thanks

Emma35 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
Date Ranges Shadowrun Queries 1 09-21-2017 04:54 AM
[SOLVED] Summing up Data between two ranges Locopete99 Modules & VBA 7 07-12-2016 04:58 AM
searching from ranges start date and end date yepwingtim Modules & VBA 3 05-25-2011 09:41 AM
Reports - Summing values based on ranges terry Reports 1 01-30-2010 10:41 AM
Date Ranges DBL Forms 4 01-06-2003 02:54 AM




All times are GMT -8. The time now is 06:59 PM.


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