Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-21-2016, 09:35 AM   #1
yetirider
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
yetirider is on a distinguished road
Identify time slots used to perform a piece of work

Hi

I have a number of time slots that cover a 24Hr period.
I need to identify which time slots a piece of work occupies and how much slot time it would use.

So the slots are:-
Name Start End SLOT_MINS
Early 00:01 01:59 118
Morning 02:00 06:59 299
Day 07:00 18:59 599
Evening 19:00 21:59 179
Late 22:00 00:00 120

So if the work starts at 01/09/2016 00:01 and ends 02/09/2016 23:00

This would cover all slots so I need to show in columns
Early Morning Day Evening Late
Plus calculate how many minutes of each slot it takes.

I have calculated the start time slot and the end time slot.

I've not managed to identify a method of using the data to identify the slots that may be used in-between and the minutes used.

I would imagine this has been done before but I can't figure this out...

Many Thanks Phil

yetirider is offline   Reply With Quote
Old 09-22-2016, 05:16 AM   #2
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,420
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Identify time slots used to perform a piece of work

I've never seen code like this, but its a slow week at work and it seemed like an interesting problem, so I made some. Attached is a database with a module to calculate how much of a work span falls within a time slot. Open the query and it shows the example you provided along with one of mine that I used to test various cases.

Few notes:

--You didn't provide table names, so I used my own.

--I don't like your field names, so I used my own.

--You used reserved words for a few field names (Name & End). You shouldn't do that. Field names need to be less than generic than that--prefix them with what they are the Start and End of and what they are the name of.

--You shouldn't store calculated fields, that means SLOT_MINS shouldn't be a field in your table. You can deduce that by other data already in that record.

--In my example a day starts at 00:00 and ends at 23:59. You had a day ending at 00:00, so that would account for any small differences.

--Your SLOT_MINS is incorrect in your example for Name=Day. There are 719 minutes in that time slot, not 599.


Let me know if you find any issues with my query or want any explanations of how I did it. I noted the function so you should be able to walk yourself through what its doing.
Attached Files
File Type: accdb TimeSlots.accdb (588.0 KB, 28 views)
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
yetirider (09-22-2016)
Old 09-22-2016, 10:07 AM   #3
yetirider
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
yetirider is on a distinguished road
Re: Identify time slots used to perform a piece of work

Many Thanks

Will download review the code.

yetirider is offline   Reply With Quote
Old 09-22-2016, 11:42 AM   #4
yetirider
Newly Registered User
 
Join Date: Jun 2012
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
yetirider is on a distinguished road
Re: Identify time slots used to perform a piece of work

That worked perfectly thanks, just had to reformat the data and convert the minutes into hh:nn

Many thanks for your help its really appreciated .
Attached Images
File Type: png slots.PNG (6.9 KB, 43 views)

yetirider 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
Create Time Slots!!!! mubi_masti Queries 2 10-06-2011 03:50 AM
Time slots dragon2309 General 0 03-07-2006 06:20 AM
help on matching time against time range slots madrav72 Modules & VBA 3 01-12-2006 07:22 AM
Time slots allocation Pauldohert General 3 03-04-2005 11:56 AM
Create Time Slots snicker Queries 3 03-17-2004 04:28 AM




All times are GMT -8. The time now is 07:47 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