Query to get Start and End

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,302
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi All

I am struggling to write a query that does this for me

I have a table called Schedule

I need to write a query that looks at this table and

first sort by Date, Agent, Start Time and End Time

Then look at the Activity column - If for an agent the word WFH appears that day then i need to get back the
Date, Agent Name, Start Time and End Time
for that day for that agent

Start Time is determined by the first time that time appears on that date for that agent and End time is the last time the time appears for that agent

Please help me write a query or VBA as last resort but will do to achieve this result

I know how to do this in Excel but will need this as a Saved Query


I know how to do this in Excel but will need this as a Saved Query

Thank You so much
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Do you know how to write Queries in Access using the Query Builder?
It sounds like this one should be pretty straightforward.
Just add the Schedule Table to the query.
Then add the Date, Agent Name, Start Time, End Time, and Activity fields to the Query.
Update the "Sort" value to "Ascending" under the first 4 fields in this Query Builder.
Then, under the "Activity" field, uncheck the "Show" box, and enter "WFH" on the Criteria row.

Now, if you view your results, I think this should give you what you want.
 
Upvote 0
Hiya

If say an agent had a day shift like this

01/01/2020 Meeting 09:00 09:15
01/01/2020 WFH 09:15 14:00
01/01/2020 Meeting 14:00 15:00

I will need to show this agent as
01/01/2020 09:00 15:00

the query builder would give me 09:15 14:00

So if an agent has WFH at any point in that day i need to retriev the first start time and the last end time that day hence before i do i this i need to ensure the data is first sorted and not sorted last

so may need 2 queries

1 to sort 1st and then get data

Hopefully im making sense
 
Upvote 0
Yes, you will need to do this either in multiple queries, or a nested query (if you are proficient at writing SQL code directly).

So, the first query gives you the record like you showed. Now, created another query that joins that query you just created back to the original table, joining on BOTH the Date and Agent Name fields. Then, do the following:

1. Have the query return the Date and Agent Name fields from the first Query
2. Also have it return the Start Time and End Time fields from the original Table
3. Click on the Aggregate Query button up in the Tool bar (looks like a Sigma or Summation sign); this will add a "Totals" row under each field being returned by the query
4. On the "Totals" row, under the "Start Date" field, change "Group By" to "Min"
5. On the "Totals" row, under the "End Date" field, change "Group By" to "Max"

Now take a look at the results. I think this should return what you are looking for.
 
Upvote 0
Thank you sooooo much
I will try that way...

how would i achieve this writing in SQL wizard?

I will attach both queries to run 1 after another in a macro
 
Upvote 0
And also i only want to retrieve the start and end only if that agent had any WFH code in their shift that day
 
Upvote 0
how would i achieve this writing in SQL wizard?
In any query in Access, you can switch to SQL View, which will show you the equivalent SQL Code of the query you just built.
However, in this View, you can also write SQL code directly, if you are proficient at doing that.

And also i only want to retrieve the start and end only if that agent had any WFH code in their shift that day
We already took care of that in the first query by putting "WFH" on the Criteria line of the Activity field.
Because the second query uses the first query, it will "inherit" that criteria.

I will attach both queries to run 1 after another in a macro
You only need to run the second.
Whenever you have a condition like this, where you have a query that uses another query, you only need to call the last one.
By calling the last one (which uses the first one), the first one will automatically be called and run.
 
Upvote 0
Are you saying 1st follow this step


Just add the Schedule Table to the query.
Then add the Date, Agent Name, Start Time, End Time, and Activity fields to the Query.
Update the "Sort" value to "Ascending" under the first 4 fields in this Query Builder.
Then, under the "Activity" field, uncheck the "Show" box, and enter "WFH" on the Criteria row.

And then follow your steps from
Post 4?
 
Upvote 0
Yes.

Post 2 will create Query1.
Post 4 will create Query2 (which uses Query1).

You will only ever need to open Query2 to get your results (opening Query2 will automatically run Query1 first, since Query2 uses Query1).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top