Query to get Start and End

[mahmed1]

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

 

[mahmed1]

So technically ill only run query 1 once to create it and then query to whenever i wana return data will always run 1 (i never knew that at all)

 

[Joe4]

Technically, it isn't so much that you are only running Query1 once. You are just building it once. Whether you actually run it or not is inconsequential.
Query2 uses query1 in its query. So, in a technical sense, it isn't so much that Query2 runs Query1 - what it actually does is assumes all the SQL code contained in it, and any queries under it.
It then tries to optimize the all the code it assumes, and run it the most efficient way it can.

So while you are creating two separate queries in Access, when Query2 is run, it combines all the code, runs it, and produces the output.

Remember what I said earlier about writing SQL code directly? If you know how to do that, you could actually write all of this yourself in one single "nested" query (or a query with a "subquery"). So, if we did that, we would just kind of be doing the step that Access is doing when we run Query2. Either way, if done right, you will get the same result.

There is nothing wrong with breaking it up until multiple queries/steps like we did here. Sometimes it makes it easier to debug. And it is certainly easier to create for beginners, as you really cannot create "nested queries" or "subqueries" yourself solely using the Query Builder (without writing some SQL code along the way).

 

[mahmed1]

thank you - your explanations are very clear and I appreciate your help

I do know a lil SQL and tried to give it a go free hand - not good but ill post what i came up with anyways

SELECT E.Date, E.Agent, Min(E.StartTime), Max(E.EndTime)

FROM

(SELECT S.Date, S.Agent, Min(S.StartTime), Max(S.EndTime)
FROM
Schedules as S
WHERE S.Activity = ‘WFH’
GROUP BY S.Date, S.Agent
ORDER BY S.Date, S.Agent, S.StartTime, S.EndTime) as E INNER JOIN ON Schedules as SCHED

WHERE E.Date = SCHED.Date
AND E.Agent = SCHED.Agent

GROUP BY E.Date, E.Agent

ORDER BY E.Date,E.Agent, E.StartTime, E.EndTime

this is the way i was going until i stumbled

 

[mahmed1]

SELECT E.Date, E.Agent, Min(E.StartTime), Max(E.EndTime)

FROM

(SELECT S.Date, S.Agent, S.StartTime, S.EndTime
FROM
Schedules as S
WHERE S.Activity = ‘WFH’
ORDER BY S.Date, S.Agent, S.StartTime, S.EndTime) as E INNER JOIN ON Schedules as SCHED

WHERE E.Date = SCHED.Date
AND E.Agent = SCHED.Agent

GROUP BY E.Date, E.Agent

ORDER BY E.Date,E.Agent, E.StartTime, E.EndTime

this is the way i was going until i stumbled

 

[Joe4]

You are pulling the wrong date fields in the Aggregates. I also changed your JOIN and ORDER BY clauses a little.
(Note that there is no point in having an ORDER BY clause in the Subquery - you aren't viewing that).

Try:
SELECT E.Date, E.Agent, Min(SCHED.StartTime), Max(SCHED.EndTime)

FROM

(SELECT S.Date, S.Agent, S.StartTime, S.EndTime
FROM
Schedules as S
WHERE S.Activity = ‘WFH’) as E

INNER JOIN Schedules as SCHED
ON (E.Date = SCHED.Date AND E.Agent = SCHED.Agent)

GROUP BY E.Date, E.Agent

ORDER BY E.Date, E.Agent

If you are still having troubles getting it to work out, here is a little secret. Follow the instructions I gave you. Then, when you have it working correctly, changing Query2 to SQL View, and copy and paste it out to something like Word. Then, do the same with Query1. and then you can manually drop Query1 into the appropriate place in Query2, and with a little cleaning up, you can create your nested/subquery that way, making Access do most of the work for you.

 

[mahmed1]

Thank you so much - I will most definitely try getting access to do it for me

I just tried to do it free hand to improve on the SQL knowledge and to see how close I could get it to the Access Code

the only reason I ordered the SUB Query is because the data in main schedule table isn’t sorted so there is no way of determining what is the actual start time and end time of that persons shift hence why I thought I’d order it)

Thank you once again

 

[mahmed1]

Ive tried to add in a Parameter - ive not got access to access at the moment so duno if this syntax is right


SELECT E.Date, E.Agent, Min(SCHED.StartTime), Max(SCHED.EndTime)

FROM

(SELECT S.Date, S.Agent, S.StartTime, S.EndTime
FROM
Schedules as S
WHERE S.Activity = ‘WFH’) as E

INNER JOIN Schedules as SCHED
ON (E.Date = SCHED.Date AND E.Agent = SCHED.Agent)

WHERE S.Date >= [Enter Start Date] AND S.Date <=[Enter End Date]

GROUP BY E.Date, E.Agent

ORDER BY E.Date, E.Agent

 

[Joe4]

the only reason I ordered the SUB Query is because the data in main schedule table isn’t sorted so there is no way of determining what is the actual start time and end time of that persons shift hence why I thought I’d order it)

Ordering a query is typically just for your viewing benefit. Since you don't view the Subquery, there really is no point in using Ordering on it.
Most of the time, Access does not really care what order the records are in. Someone once gave a very appropriate description of how Access treats a data table. They said it is like a "bag or marbles", order really has no meaning to a relational database table.

There are certain instances in which it does matter, like if you are trying to use functions that look at the previous records, or if you are using VBA to iterate through a Recordset in a certain order. In this case, it does not matter, as we are simply grouping by Agent and Date, and taking the minimum Start Time and maximum End Time for each grouping. So order would have zero effect on that.

 

[mahmed1]

Thank you sooooo much

 

[Joe4]

You are welcome.