Identifying mismatched sets with two non-unique criteria

[Joel Marion]

First, my apologies if the title of this post is overly cryptic. I have a data set identifying events (academic courses) including start and end times. I want to verify that the times entered fit into a prescribed set of start and end times (slots), and spit out a report flagging courses that do not fit the prescribed slots. I also need to exclude courses with Section and/or Location codes that don't need to be checked.

I have the following tables:
Output: Course, Sec, Start, End, Location
Slots: Slot, SlotStart, SlotEnd, Term
Roomless (location codes): ID, OffcLoc
RoomlessSection s: id, Section

My process is:
1. exclude Output.Sec = RoomlessSection s.Section
2. exclude Output.Location = Roomless.OffcLo c
3. identify Slots.Slot where Output.Start = Slots.SlotStart
4. identify Slots.Slot where Output.End = Slots.SlotEnd
5. produce a report with the following:

a. Start not found in Slots
b. End not found in Slots
c. Start and End not in matching Slot

Further complicating things, the Slot Start and End times are not unique values (For example: Slot 6 is 11:30-12:20 and Slot 9 is 11:30-12:45), so I have to compare a list of possible slots based on each of Start and End to see if there is a match.

My approach has been to first try to produce the results in separate queries, thus:

Possible Start Slots:

SELECT Output.ID, Slots.Slot AS PossibleStartSlot
FROM [Output] LEFT JOIN Slots ON (Output.Start=Slots.SlotStart) AND (Output.Term=Slots.TermUnique);

and Possible End Slots:

SELECT Output.ID, Slots.Slot AS PossibleEndSlot
FROM [Output] LEFT JOIN Slots ON (Output.End = Slots.SlotEnd) AND (Output.Term = Slots.TermUnique);

Then compare the two:

SELECT Output.ID, qryPossibleStartSlots.PossibleStartSlot, qryPossibleEndSlots.PossibleEndSlot
FROM [Output] INNER JOIN (qryPossibleStartSlots INNER JOIN qryPossibleEndSlots ON (qryPossibleStartSlots.PossibleStartSlot = qryPossibleEndSlots.PossibleEndSlot) AND (qryPossibleStartSlots.ID = qryPossibleEndSlots.ID)) ON Output.ID = qryPossibleStartSlots.ID;

Finally, I use a fourth Query to analyze the data:

SELECT Output.Department AS Department, Output.Course AS Course, Output.Sec AS Sec, Output.Title AS Title, Output.Start1 AS Start1, Output.End1 AS End1, Output.Start AS Start, Output.End AS [End], IIf([Output].[ID] In (select ID from qryMatchedStartEndSlots),"",IIf(IsNull([Output].[Start]),"No Start Time","Start Time Mismatch")) AS StartCheck, IIf(IsNull(Output.End),"No End Time","End Time Mismatch") AS EndCheck INTO SlotCheck
FROM ([Output] LEFT JOIN RoomlessSections ON Output.Sec=RoomlessSections.Section) LEFT JOIN Roomless ON Output.Location=Roomless.OffcLoc
WHERE (([Output].[Location] Not In (Select OffcLoc from Roomless)) AND ([Output].[Sec] Not In (Select Section from RoomlessSections)));

I'm using a make table so that I can run the report separate from the query, as (I realize) running four queries like this is extremely inefficient and slow.

I know I'm going about this the hard way, but I don't know how else to build this kind of comparison.

Extra Info :
My Slots table looks like this:

Slot  SlotStart	SlotEnd	Term
1	  8:30 AM	 9:20 AM	FW
2	  9:30 AM	10:20 AM	FW
3	 10:30 AM	11:20 AM	FW
4	  8:30 AM	 9:45 AM	FW
5	 10:00 AM	11:15 AM	FW
6	 11:30 AM	12:20 PM	FW
7	 12:30 PM	 1:20 PM	FW
8	  1:30 PM	 2:20 PM	FW
9	 11:30 AM	12:45 PM	FW
10	 1:00 PM	 2:15 PM	FW
[B][u]11	 2:30 PM	 3:45 PM	FW[/u][/B]
12	 4:00 PM	 5:15 PM	FW
[B][u]13	 2:30 PM	 3:45 PM	FW[/u][/B]
14	 4:00 PM	 5:15 PM	FW
[B][u]15	 2:30 PM	 5:15 PM	FW[/u][/B]

Notice how a number of start and end times repeat, and not necessarily in the same pairings. ie: 2:30 pm SlotStart shows up three times, with two different End times.

So, if I have a course with a 2:30 start, I need to be able to accept 3:45 pm or 5:15 pm as valid End times. This is why I'm trying to identify along the lines of 'if a 2:30 start could possibly be slots 11, 13 or 15, does my end time match the end times for these slots.'

So far, my efforts don't seem to be working. I think my problem is in my JOINs, but I'm not familiar enough with this kind of query to understand why.

[ADezii]

This can probably be attempted in Code, if you like I'll give it a shot when I hqqve some time.

[Joel Marion]

I'd love the help. If you can even suggest the type of method you might use I can start looking. I'm just so new to VBA I don't know where to start.

[ADezii]

In general, the following Code will:

1. Accept Input from the User for the Start and End Times. This is accomplished via 2 Text Boxes (txtStartTime and txtEndTime) on a Form.
2. Check the Start Time entered with existing Values in the Slots Table.
3. If the Start Time does not exist in Slots, notify the User then Exit.
4. If the Start Time exists in Slots, check the entered End Time against End Times in Slots for the given Start Time.
5. If the End Time does not exist (Start Time does), notify User and Exit.
6. If both the Start and End Times are Valid Entries, post the available Slots (1 to 15) in a List Box (lstSlots) on the Form.
7. Realizing that all this may be totally confusing to you, in addition to the posted Code, I've also Attached a Demo that I created for you.
8. Study the Demo carefully, and should you have any questions, feel free to ask.

Dim strSQL As String
Dim MyDB As DAO.Database
Dim rstSlots As DAO.Recordset

Me![lstSlots].RowSource = ""        'Clear ListBox

'Need Values for both Start and End Times
If IsNull(Me![txtStartTime]) Or IsNull(Me![txtEndTime]) Then Exit Sub

If DCount("*", "Slots", "[SlotStart] = #" & Me![txtStartTime] & "#") = 0 Then
  MsgBox "No available Slots for the specified Start Time of [" & Format$(Me![txtStartTime], "Medium Time") & _
         "]", vbExclamation, "No Matching Start Time"
           Exit Sub
Else    'Start Time Matches, but does the End Time?
  If DCount("*", "Slots", "[SlotStart] = #" & Me![txtStartTime] & "# AND [SlotEnd] = #" & _
     Me![txtEndTime] & "#") = 0 Then
    MsgBox "No available Slots for the specified Start/End Time combination", vbExclamation, _
           "No Matching Start/End Time Combination"
             Exit Sub
  Else
    'There is at least 1 Slot available for the Start and End Times, what is it/are they?
    strSQL = "SELECT * FROM Slots WHERE [SlotStart] = #" & Me![txtStartTime] & "# AND [SlotEnd] = #" & _
              Me![txtEndTime] & "# ORDER BY [Slot]"
    Set MyDB = CurrentDb
    Set rstSlots = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
     Me![lstSlots].AddItem "Slot;Start;Stop;Term"
      With rstSlots
        Do While Not .EOF
          Me![lstSlots].AddItem ![Slot] & ";" & ![SlotStart] & ";" & ![SlotEnd] & ";" & ![Term]
            .MoveNext
        Loop
      End With
  End If
End If

rstSlots.Close
Set rstSlots = Nothing

P.S. - I see no way how this can be accomplished via SQL, but I am not the person to ask regarding this. Hopefully, NeoPa, Rabbit, or one of the other SQL Phenoms will see this Thread and give their opinion.

[Rabbit]

Basically you want to find

a. Start not found in Slots
b. End not found in Slots
c. Start and End not in matching Slot

But do you even need c? If they're in c then they must be in either a or b.

SELECT o.*
FROM Output AS o
LEFT JOIN Slots AS s
ON o.[Start] = s.SlotStart
WHERE s.SlotStartIS NULL

UNION

SELECT o.*
FROM Output AS o
LEFT JOIN Slots AS s
ON o.[End] = s.SlotEnd
WHERE s.StartTime IS NULL

[Joel Marion]

Rabbit: yes, c is where I check whether or not courses that are found in both start and end slots have a matching slot number (if they do, I want to exclude them from my report).

ADezii: I haven't had a chance to take a look at your example, but thank you for providing it!

[Rabbit]

What I'm trying to say is there's no need to check for c. Because if they fall under c, they must necessarily fall under a or b. The query should be pretty close to what you're looking for.

[NeoPa]

Rabbit:
Basically you want to find

a. Start not found in Slots
b. End not found in Slots
c. Start and End not in matching Slot

But do you even need c? If they're in c then they must be in either a or b.

I imagine that should probably read :

a. [Start] but not [End] found in Slots.
b. [End] but not [Start] found in Slots.
c. Neither [Start] nor [End] found in Slots

This includes all the same records, but simply separates them in the report into different batches.

If so, then your concept is the approach that I would have recommended. It has the beauty of clearing away duplicates automatically from the result set, which is inherent in the UNION clause.

What it won't do as it stands though, is indicate the status values required for the report, as only the [Output] data is included in the SELECT clause. It's certainly a very important basis from which to start though.

SELECT tS.SlotStart
     , tS.SlotEnd
     , tO.*
FROM   [Output] AS [tO]
       LEFT JOIN
       [Slots] AS [tS]
  ON   tO.Start = tS.SlotStart
WHERE  (tS.SlotStart IS NULL)
  AND  (tO.Sec Not In(SELECT [Section]
                      FROM   [RoomlessSections]))
  AND  (tO.Location Not In(SELECT [OffcLoc]
                           FROM   [Roomless]))

UNION

SELECT tS.SlotStart
     , tS.SlotEnd
     , tO.*
FROM   [Output] AS [tO]
       LEFT JOIN
       [Slots] AS [tS]
  ON   tO.End = tS.SlotEnd
WHERE  (tS.SlotEnd IS NULL)
  AND  (tO.Sec Not In(SELECT [Section]
                      FROM   [RoomlessSections]))
  AND  (tO.Location Not In(SELECT [OffcLoc]
                           FROM   [Roomless]))

[Joel Marion]

Rabbit, you're right that if something falls under a. and b. it would necessarily fall under c., making c. redundant. But the c. I'm looking for is whether or not, in cases when [Start] and [End] are found in Slots, whether or not the slots (or possible slots) for each match.

Thus:
a. [Start] but not [End] found in Slots ("End time mismatch")
b. [End] but not [Start] found in Slots ("Start time mismatch")
c. [Start] and [End] found in Slots, but Slots don't match

Option C example (matching slots):
[Start]= 2:30 PM
[End] = 3:45 PM
I need to figure out that [Start] can be either slot 11 or 13, and I need to compare this to [End] which can be (in this example) either slot 11 or 13. This example would indicate a 'normal' course that doesn't need to be flagged.

In some cases there will be multiple matching slots (as in the above example), in some cases only one slot option will match, and in other cases no slot option will match.

Option C example 2 (non-matching slots):
[Start] = 1:00 PM
[End] = 3:45 PM
In this case, [Start] is slot 10 and [End] could be either slot 11 or 13. Because the possible slots don't match, I want to flag this course as being outside of the normal slot system.

The code offered by NeoPa in the previous comment provides for Options a. and b. but naturally excludes c.

I think I'm going to need to run a separate query to do the slot matching. How would I identify the possible slots in order to compare them? This is a bit of a shot in the dark, but could I store the options in an array in a DO loop?

[Rabbit]

As long as it doesn't matter what the actual error classification is, you just need to add an additional union query to my original suggestion.

SELECT o.*
FROM Output AS o 
LEFT JOIN Slots AS s 
ON o.[Start] = s.SlotStart 
WHERE s.SlotStart IS NULL 
  
UNION
  
SELECT o.* 
FROM Output AS o 
LEFT JOIN Slots AS s 
ON o.[End] = s.SlotEnd 
WHERE s.StartTime IS NULL  
  
UNION
  
SELECT o.* 
FROM Output AS o 
LEFT JOIN Slots AS s 
ON o.[Start] = s.SlotStart AND o.[End] = s.SlotEnd 
WHERE s.StartTime IS NULL

[NeoPa]

I'm starting to realise this concept won't work as intended :-( I know Rabbit will correct me if I go astray, but here's my latest thinking.

It seems that the original question was mis-expressed, as point 5c (Originally a second 5a, but that's fixed as I just noticed it now.) clearly stated that records where neither [Start] nor [End] were required, whereas post #6 indicates categorically they now are not. Not that this was the reason for the earlier SQL being wrong at all, at least not as far as I'm concerned, but it does effect the SQL we're now looking for of course.

To make life simpler I'll create SQL sets that can be saved as QueryDefs in the database. This can all be done with subqueries in SQL etc (See Subqueries in SQL.) but it can lead to some very complicated (verbose) SQL with many repetitions (especially using Jet SQL as is native in Access).

qryOutputNoRoom s :

SELECT tO.*
FROM   [Output] AS [tO]
WHERE  (tO.Sec Not In(SELECT [Section]
                      FROM   [RoomlessSections]))
  AND  (tO.Location Not In(SELECT [OffcLoc]
                           FROM   [Roomless]))

qryOutputMatch :

SELECT qONR.Start
     , qONR.End
FROM   [qryOutputNoRooms] AS [qONR]
       INNER JOIN
       [Slots] AS [tS]
  ON   (qONR.Start = tS.SlotStart)
 AND   (qONR.End = tS.SlotEnd)

qryResults :

SELECT qONR.*
     , tSS.SlotStart
     , tSE.SlotEnd
FROM (([qryOutputNoRooms] AS [qONR]
       LEFT JOIN
       [qryOutputMatch] AS [qOM]
  ON   (qONR.Start = qOM.Start)
 AND   (qONR.End = qOM.End))
       LEFT JOIN
       [Slots] AS [tSS]
  ON   (qONR.Start = tSS.SlotStart))
       LEFT JOIN
       [Slots] AS [tSE]
  ON   (qONR.End = tSE.SlotEnd)
WHERE  (qOM.Start IS NULL)
  AND  (
       ((tSS.SlotStart IS NULL) AND (tSE.SlotEnd IS NOT NULL))
   OR  ((tSS.SlotStart IS NOT NULL) AND (tSE.SlotEnd IS NULL)))

I should clarify that tSS.SlotStart and tSE.SlotEnd are there to be used in the report to indicate what type of problem has been identified. A Null in either position indicates the missing match.

[Rabbit]

You know, I think it's been overcomplicated . Isn't the actual goal to identify everything without an exact match? Isn't this all that's needed?

SELECT o.*  
FROM Output AS o  
LEFT JOIN Slots AS s  
ON o.[Start] = s.SlotStart AND o.[End] = s.SlotEnd  
WHERE s.SlotStart IS NULL
   AND o.Sec Not In (SELECT Section FROM RoomlessSections) 
   AND o.Location Not In (SELECT OffcLoc FROM Roomless)

Yes, this doesn't identify the "error" but I don't think that was needed in the first place. All that was needed was to identify the records.

[NeoPa]

Having just read Joel's latest explanation of point 5c in post #9 I was further confused, so I went back over post #6 to realise I'd been confused by the explanation there last time. I think I eventually understand what he was trying to say (which was closer to my original understanding of it before the explanation) and this means that the solution I provided in post #11 has three extraneous lines at the end. Essentially, lines #16 through #18 are not necessary for this scenario. The SQL finishes neatly at line #15 and if situation 5c occurs then neither tSS.SlotStart nor tSE.SlotEnd will be Null.

[NeoPa]

Rabbit:
Isn't this all that's needed?

I don't believe so. That doesn't provide information with which to indicate which of the three scenarios (5a, 5b or 5c) each record falls into.

Rabbit:
but I don't think that was needed in the first place. All that was needed was to identify the records.

I'm not sure why you'd say that. Point #5 seems to specify the requirement to identify which type each failed record falls into.

[Rabbit]

I don't believe so. That doesn't provide information with which to indicate which of the three scenarios (5a, 5b or 5c) each record falls into.

I would contend that this information was not needed in the first place. I think the OP was only identifying the types of records they want to see rather than indicating that it also be part of the result set.

P.S. Nevermind, I was wrong. I took a look at the original post's fourth query and the type of error was in there.