The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
Employee Shift Calculation in Access
I recently asked a question and it was answered successfully, however now I am adding to the originally asked question adding some additional scenarios. Pls see Attachment.
Anna tab shows the data (table) I have in access. The Results wanted tab shows what I want to display after my data is quried for Start of shift and End of shift. There are multiple services logged during a shift but I just want the Start and End time of the shift and not all the detail in between. AS I stated there are multiple scenarios. Day shift, PM Shift Over night shift etc. Pls see Attachment. I think I chose the wrong Zoning originally. I want to do this in Access Database as my table is stored in Access.
Anna.xls
Anna tab shows the data (table) I have in access. The Results wanted tab shows what I want to display after my data is quried for Start of shift and End of shift. There are multiple services logged during a shift but I just want the Start and End time of the shift and not all the detail in between. AS I stated there are multiple scenarios. Day shift, PM Shift Over night shift etc. Pls see Attachment. I think I chose the wrong Zoning originally. I want to do this in Access Database as my table is stored in Access.
Anna.xls
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Unfortunatly no. This is a on demand service business and an employee can work many different types of shifts as they schedule their own services to meet customer needs.
It is not consistent from day to day the amount of hours between an employees shift. could be 6 up to 48 as they take days off. They receive reqeusts for service, they call cust sched a svc time w customer and they log their start and end time for each service, but there is no consistancy in days off, or hours between shifts. Could be 6 Hrs up to 48. So your saying I will not be able to get the results I am looking for.
As long as you are saying that the MINIMUM gap between shifts is 6 hours then you can handle the problem. It doesn't matter if the actual gap is greater than 6 hours.
The below question is the question I posted originally March 2nd, and was answered successfuly. This worked for the cross over midnight shift but didn't work for other scenarios. How could I adjust this qry to work with all scenarios utilizing the 6 hours as gap time.
Question: I want the Max PM Start Time each day and the Max AM End Time the following day
This will then reach the shift worked starting with first acct worked start time and last account
worked end time. I want this for each day. I am looking for qry to do this in access as my data is stored in an access table.
There are multiple start and end times of svc I just want to see first of shift and end of shift.
So it looks like this.
Start Time: End Time: Hours Worked
11/8/10 10:10 PM 11/9/10 2:39 AM 4.23
11/9/10 7:52 PM 11/10/10 12:14 AM 4.22
MaxEndTime:
SELECT FORMAT(anna.[End Time],"Medium Date") AS EndTime,
MAX([End Time]) AS MaxEndTime
FROM anna
WHERE HOUR([End Time]) < 12
GROUP BY FORMAT(anna.[End Time],"Medium Date");
MinStartTime:
SELECT FORMAT(anna.[Start Time],"Medium Date") AS StartTime,
MIN([Start Time]) AS MinStartTime
FROM anna
WHERE HOUR([Start Time]) >= 12
GROUP BY FORMAT(anna.[Start Time],"Medium Date");
Now query these two queries.
SELECT t1.MinStartTime,
t2.MaxEndTime
FROM (MinStartTime AS t1
INNER JOIN MaxEndTime AS t2
ON DATEADD("d",1,t1.StartTime
Question: I want the Max PM Start Time each day and the Max AM End Time the following day
This will then reach the shift worked starting with first acct worked start time and last account
worked end time. I want this for each day. I am looking for qry to do this in access as my data is stored in an access table.
There are multiple start and end times of svc I just want to see first of shift and end of shift.
So it looks like this.
Start Time: End Time: Hours Worked
11/8/10 10:10 PM 11/9/10 2:39 AM 4.23
11/9/10 7:52 PM 11/10/10 12:14 AM 4.22
MaxEndTime:
SELECT FORMAT(anna.[End Time],"Medium Date") AS EndTime,
MAX([End Time]) AS MaxEndTime
FROM anna
WHERE HOUR([End Time]) < 12
GROUP BY FORMAT(anna.[End Time],"Medium Date");
MinStartTime:
SELECT FORMAT(anna.[Start Time],"Medium Date") AS StartTime,
MIN([Start Time]) AS MinStartTime
FROM anna
WHERE HOUR([Start Time]) >= 12
GROUP BY FORMAT(anna.[Start Time],"Medium Date");
Now query these two queries.
SELECT t1.MinStartTime,
t2.MaxEndTime
FROM (MinStartTime AS t1
INNER JOIN MaxEndTime AS t2
ON DATEADD("d",1,t1.StartTime
Here is a sample solution based on your data.
Open the file and look at the two tables- one is the raw data, and the other is an enpty tabe where the calculated shifts will go.
Close the tables.
Click the button on the form and then look at the results table.
The code that does all this is in module 6.
Database12.mdb
Open the file and look at the two tables- one is the raw data, and the other is an enpty tabe where the calculated shifts will go.
Close the tables.
Click the button on the form and then look at the results table.
The code that does all this is in module 6.
Database12.mdb
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
is there any relation between Employees and shifts?, is there a table that says that employee X is working on shift type M ?