Find times not accounted for between two queries

[MaryGS]

I have a query with a start and end time for the employee shift. I have a second query that pulls activities scheduled throughout the day with start and end times. Employees have a "base" schedule that they should follow for all times not listed in the second query. I'd like to pull it altogether in one query to list the base and exceptions for the full day. I hope this makes sense and someone can help.



Thank you,
Mary

[ItsMe]

Maybe you can search for examples of an appointment database. I did a search and found the following two. However, these examples seem familiar to me. I seem to remember certain tactics that I do not agree with or would not use the example(s).
https://www.youtube.com/watch?v=hQuXVxGFm7s

https://www.youtube.com/watch?v=hQuXVxGFm7s

And it seems like the other examples that may be promising are not free. If you need help with something, you are going to need to provide a more specific issue that you are struggling with.

For example:

I have a query to retrieve dates with appointments but I need to create another query that shows available appointments, Here is the SQL for the query that works and retrieves appointments.

[MaryGS]

I've looked at the appointment databases with no luck, this is probably over my head.

Here is my query:

SELECT
qryScheduleBase.EmployeeID,
qryScheduleBase.Icon,
qryScheduleBase.StartDate,
qryScheduleExceptions.StartTime,
qryScheduleExceptions.EndTime,
ryScheduleBase.BaseStart,
qryScheduleBase.BaseEnd

FROM qryScheduleBase LEFT JOIN qryScheduleExceptions
ON (qryScheduleBase.StartDate = qryScheduleExceptions.StartDate)
AND (qryScheduleBase.EmployeeID = qryScheduleExceptions.EmployeeID);

StartDate	StartTime	EndTime	BaseStart	BaseEnd
11/24/2015	10:30	11:30	10:30	19:00
11/24/2015	11:30	12:00	10:30	19:00
11/24/2015	13:00	14:00	10:30	19:00
11/24/2015	14:00	14:30	10:30	19:00
11/24/2015	14:30	15:00	10:30	19:00
11/24/2015	18:00	19:00	10:30	19:00

I'm hoping to find a way to show the StartTime and EndTime for periods not accounted for. In the example above, 12:00 to 13:00 should be listed as Base. 15:00 to 18:00 should be listed as Base. All of the rows currently listed above are Exception.

[ItsMe]

I have never built an appointments database. I have, but probably not implementing the feature you are after. Usually, I would recommend using an application like Outlook, which can be referenced by your Access application.

What happens here is you need to create a logical representation of what is left as available. So, the item that is available is not a field in a table with data. It is a logical field that would be created as needed. One example might be an alias in a query. You can create a column in a query, as an alias, to show calculations.

When I look at your subqueries and your base start and base end, I start to imagine many variables. For instance, a doctors office might have a fixed start time, a fixed lunch hour, and a fixed end time. It seems your business rules are different. All of these times change. This will make it even more difficult to produce a virtual field or an alias, if you will.

I have come across similar issues with reporting for back office stuff. Everyone is familiar with the rule, do not store calculated data. Well, sometimes it is necessary to break this rule. For instance, certain reporting is necessary when considering business rules for Accounting. It can be easier to manage if you store calculated data in tables. It is easier or more practical to persist the calculated data.

Another example might be reporting for fiscal periods. I will create tables to store and reference dates. These dates help me to build a calendar. If I did not have the tables, I would need to use Constants in the computer's memory. I find it easier to join on a table than use something less tangible, like a constant (and I am a big fan of code centric applications).

What I am trying to get at here, is that it might be helpful to use tables to store constants like dates. Maybe some tables to store your Base Start and Base End. I know these seem like variables. However, it should be possible to enumerate them all and place the values in tables. You can write code to enumerate and append the tables. Other tables that are part of operations can use key values in these tables to define start and end.

After creating a few tables and playing around with the idea, you may see it as helpful. You might be able to imagine a query that shows what is available because it uses one of these tables that store the constants. If a table or query has a record that represents every available appointment, you could use that to compare to a query of pending appointments.

[MaryGS]

I decided to try this in SQL Server 2012 and was able to get what I need using the LAG function to reference the previous record. Thanks for the tips.

[ItsMe]

I decided to try this in SQL Server 2012 and was able to get what I need using the LAG function to reference the previous record. Thanks for the tips.

Thanks for posting your solution and glad to hear you have things under control. FYI, you can call custom functions from within a query in Access. So, an alternative to the LAG function in SQL server would be to place a DLookup() in a standard module and call that custom function procedure. However, this is a real good way to slow down Access and would not compare to the performance of LAG in SQL Server.