Identifying mismatched sets with two non-unique criteria

[NeoPa]

Rabbit:
Nevermind, I was wrong.

At last!

Not that I feel any form of schadenfreude. Simply that I was starting to feel that every time we disagreed I turned out to be the one having to admit I was wrong. You still win on points of course, but at least it's no longer a whitewash :-D

[Rabbit]

I think neither of our queries work. Because of the hitherto unforeseen 5d error. Where neither start nor end is in the table.

o.Start o.End type
01:00   02:00
03:00   04:00 a
05:00   06:00 b
01:00   06:00 c 
05:00   04:00 d

s.Start s.End
01:00   02:00
03:00   06:00

[NeoPa]

I believe the SQL in post #11 (excluding lines #16 through #18 as amended in post #13) handles that OK. If you're convinced otherwise I'll have to knock up a test-rig to test it out, but I thought that was handled automatically in my code.

[Rabbit]

I would have try some sample data but I don't think your solution would identify c or d errors. I believe the errors are defined as:

a. start is in one of the records in the table but end is not in any of the records.
b. end is in one of the records in the table but start is not in any of the records.
c. either start or end is in one of the records in the table but the other is not in any of the records.
d. (new) start and end does not exist is any of the records in the table.

[NeoPa]

It appears there's still some confusion over the specification as described in point #5. Let me see if I can clarify things a little :

Possible Scenarios :

1. A matching [Slots] record is found where Output.Start = Slots.SlotStart and Output.End = Slots.SlotEnd.
2. [Slots] record(s) are found that match Output.Start = Slots.SlotStart but not Output.End = Slots.SlotEnd.
3. [Slots] record(s) are found that match Output.End = Slots.SlotEnd but not Output.Start = Slots.SlotStart .
4. There are [Slots] records that match Output.Start = Slots.SlotStart and Output.End = Slots.SlotEnd, but none that do both.
5. Neither Output.Start nor Output.End have matching values anywhere in [Slots].

My understanding is that all records are required from [Output] except those in scenario #1.

As such, I believe your scenario d was, indeed, previously unspecified, and matches scenario #5 above. However, your scenario c describes a set consisting of scenarios #2 & #3 above, rather than scenario #4.

All that being said, I would expect my code, as it works on the simple basis of excluding scenario #1 records, to cover all the other scenarios adequately (unless I have a flaw in my logic somewhere).

The values contained in Slots.SlotStart and Slots.SlotEnd will indicate which scenario a particular record belongs to as follows :

1. This never occurs due to line #15 in SQL.
2. Slots.SlotStart Is Not Null but Slots.SlotEnd Is Null.
3. Slots.SlotStart Is Null but Slots.SlotEnd Is Not Null.
4. Neither of Slots.SlotStart nor Slots.SlotEnd is Null.
5. Both of Slots.SlotStart and Slots.SlotEnd are Null.

[NeoPa]

As the last three lines of that SQL may still be muddying the waters for people, and also because, now UNION is no longer included in the mix there may be duplication of records in my previous version, I will post an amended version which includes the DISTINCT predicate to handle that likelihood (It also saves having to scroll down so far to check the details) :

qryResults :

SELECT DISTINCT 
       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)

[qryOutputNoRoom s] and [qryOutputMatch] may still be found in their original versions in post #11.

PS. I also found a mis-reference in the original to tO.* in the SELECT line which has now been fixed in the original.

[Rabbit]

My bad, you are absolutely right. I just had to work it out on paper to see it.

[NeoPa]

: - )

[Joel Marion]

I've been trying to post since Friday, and the Bytes server keeps timing out on me, so here goes again:

To recap: I'm trying to identify
a. [Start] but not [End] found in Slots ("End is out of slot")
b. [End] but not [Start] found in Slots ("Start is out of slot")
c. [Start] and [End] are found in Slots, but Slots don't match ("cross-slot")

Which I've tried to break down into some logic:

qrySlotCheck:

SELECT qOWR.*, 
IIF([Start] Not In (SELECT [SlotStart] FROM [Slots]),
     IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]),
          "Start and End Time out of slot",
          "Start Time out of slot"
          ),
     IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]),
          "End Time out of slot",
          IIF([qOWR].[ID] In (SELECT [MatchedID] FROM [qryMatched]),"normal", "cross-slot section")
          )
) AS SlotCheck
FROM [qryOutputWithRooms] AS [qOWR];

qryMatched:

SELECT 
qOWR.*, qOWR.ID AS MatchedID
, tSS.Slot
, tSE.Slot
FROM ([qryOutputWithRooms] AS [qOWR]
LEFT JOIN
[Slots] AS [tSS]
ON (qOWR.Start = tSS.SlotStart AND qOWR.Term = tSS.TermUnique))
LEFT JOIN
[Slots] AS [tSE]
ON (qOWR.End = tSE.SlotEnd AND qOWR.Term = tSE.TermUnique)
WHERE (tSS.SlotStart IS NOT NULL) AND (tSE.SlotEnd IS NOT NULL) AND (tSS.Slot = tSE.Slot);

(note that I renamed qOWR.ID here because I was getting an error in my main query due to ID showing up twice.)

and qryOutputWithRo oms:

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

I think this is giving me the results I need, but I have to spend some time with the results to be sure.

I do have two major concerns with this code:

1. qryOutputWithRo oms produces 1607 records, while qrySlotCheck produced 1591 records. I need to compare the records to see which ones were dropped, but it gets tricky due to the second issue:

2. Running qrySlotCheck took nearly half an hour! I think I'm approaching some record for inefficient code. I'm sure this is because I'm running huge sub-queries against each record, but I'm still working on finding another way to produce the results.

I hope the logic in qrySlotCheck clears up some of the confusion about what it is I'm looking for. Specifically, I need to be able to produce explicit comments regarding which of the three issues noted above is being flagged.

I did consider trying some kind of flagging in a UNION, like

SELECT qOWR.*
[B], IIF([tSS].[SlotStart] = "", "Start Time out of Slot","") AS Check[/B]
FROM [qryOutputWithRooms] AS [qOWR]
LEFT JOIN
[Slots] AS [tSS]
ON (qOWR.Start = tSS.SlotStart)

UNION

SELECT qOWR.*
[B], IIF([tSE].[SlotEnd] = "", "End Time out of Slot","") AS Check[/B]
FROM [qryOutputWithRooms] AS [qOWR]
LEFT JOIN
[Slots] AS [tSE]
ON (qOWR.End = tSE.SlotEnd);

Even if I used this as the first half of a query that then went on to check matched slots, this doesn't allow for the specific reporting I'm looking for (particularly where both Start and End are not found).

Would it be better to run the whole thing in VBA, turn my sub-queries into make tables at the beginning of the process, and delete them when I'm done with them? I would think this would at least speed things up.

[Joel Marion]

I decided to run with the VBA idea, and built my SQL into some VBA, using make tables instead of queries. This sped things up considerably.

Here's what I came up with:

Private Sub SlotChecker()
On Error GoTo Err_SlotChecker

Dim strSQL As String
Dim fileName As String

DoCmd.SetWarnings False

If Me!fileName = "" Then
MsgBox "file name is blank"
Call RunQryCurrVerAnalysis_Click
End If

' make a new Output Table with Rooms (tOWR)
strSQL = "SELECT tO.* INTO tOWR " & _
        "FROM [Output] AS [tO] " & _
        "WHERE (tO.Sec Not In(SELECT [Section] " & _
                      "FROM   [RoomlessSections])) " & _
        "AND  (tO.Location Not In(SELECT [OffcLoc] " & _
                           "FROM   [Roomless]));"
 DoCmd.RunSQL strSQL
 
' make a new Matched Sections Table
strSQL = "SELECT tOWR.*, tOWR.ID AS MatchedID, tSS.Slot, tSE.Slot INTO tMatched " & _
            "FROM (tOWR LEFT JOIN Slots AS tSS ON (tOWR.Start=tSS.SlotStart) AND " & _
            "(tOWR.Term=tSS.TermUnique)) LEFT JOIN Slots AS tSE ON (tOWR.End=tSE.SlotEnd) AND " & _
            "(tOWR.Term=tSE.TermUnique) " & _
            "WHERE (tOWR.Start Is Not Null) AND (tOWR.End Is Not Null) AND (tSS.SlotStart Is Not Null) " & _
            "And (tSE.SlotEnd Is Not Null) And (tSS.Slot=tSE.Slot);"

DoCmd.RunSQL strSQL
 
' build and run slot check query
strSQL = "SELECT tOWR.*, IIF([Start] Not In (SELECT [SlotStart] FROM [Slots]), " & _
        "IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]), " & _
          "'Start and End Times out of slot', 'Start Time out of slot'), " & _
        "IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]), " & _
          "'End Time out of slot', " & _
          "IIF([tOWR].[ID] In (SELECT [MatchedID] FROM [tMatched]),'normal', 'cross-slot section'))) AS SlotCheck " & _
          "INTO tSlotCheck " & _
          "FROM tOWR; "

DoCmd.RunSQL strSQL

' Call up the report
    stDocName = "Slot Check"
    DoCmd.OpenReport stDocName, acPreview


Exit_SlotChecker:
   DoCmd.SetWarnings True
   DoCmd.Hourglass False
    Exit Sub

Err_SlotChecker:
    DoCmd.Hourglass False
    MsgBox Err.Description
    Resume Exit_SlotChecker
End Sub

[NeoPa]

As a matter of interest Joel, might we know why you decided to ignore the SQL suggested and go off at a tangent?

I can see why you decided to go with VBA when your SQL failed, but why change the SQL at all in the first place? It doesn't seem a very logical approach (I'm English so I'm allowed to get away with such heavy understatement) . Did you find it didn't work?

Unfortunately, I cannot help you if you take that approach. There's not even feedback to explain why it isn't used, so there's no logic path even to try to follow here. Clearly I'm interested in your response, but beyond that I can only wish you good luck with your project and console myself that the hours put into it were educational for me and, hopefully, other readers of this thread too.

[Joel Marion]

I hope I gave some indication in post #24 regarding the processing time of the SQL approach (20-30 minutes for 1600 records) which led me to change approach by using make tables instead of queries. As for the change to VBA, I had two reasons. First, I needed something that would clearly delimit which criteria each record triggers, and that required at least some additional code from where we were with the SQL; second, the logic had a clear bifurcation between records with valid start/end times and those without. This required (from my understanding) splitting the logic into an if statement that could handle four different combinations - something that seemed to get overly complicated in SQL. All of the suggestions offered here gave me a much better understanding of the way to approach the problem (and helped me build the queries in my solution), so I would say your contributions were quite helpful, and not a waste at all. When it came down to it, I needed to take your suggestions on how to identify the different combinations and turn it into something that could run faster, and spit out the detailed analysis I was looking for, which seems to run much more elegantly in VBA.

Thanks for all your help! I truly do appreciate it, and would not have come to a solution without it.

[NeoPa]

Joel:
I hope I gave some indication in post #24 regarding the processing time of the SQL approach

You certainly gave an indication of the problems with the SQL you came up with. I was trying to make the point that you hadn't commented on the SQL I suggested. Clearly (not from any comments but from reading between the lines) you felt that there was information missing from the SQL suggested - which is unfortunate as it was there if you had recognised it - and decided to add chunks that would certainly be expected to reduce efficiency drastically. This is why an ongoing dialogue of clear communication is advisable when working on a forum. I cannot help you to understand any confusion you clearly had comprehending the SQL accurately, if you never express it.

Never mind. I'm sure you're happy enough with your solution, and clearly this is all at a fairly complex level anyway, so finding a perfect way of handling it all (including responses etc) was always going to be less than straightforward .