By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Identifying mismatched sets with two non-unique criteria

P: 22
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
RoomlessSections: id, Section

My process is:
1. exclude Output.Sec = RoomlessSections.Section
2. exclude Output.Location = Roomless.OffcLoc
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Output.ID, Slots.Slot AS PossibleStartSlot
  2. FROM [Output] LEFT JOIN Slots ON (Output.Start=Slots.SlotStart) AND (Output.Term=Slots.TermUnique);
and Possible End Slots:
Expand|Select|Wrap|Line Numbers
  1. SELECT Output.ID, Slots.Slot AS PossibleEndSlot
  2. FROM [Output] LEFT JOIN Slots ON (Output.End = Slots.SlotEnd) AND (Output.Term = Slots.TermUnique);
Then compare the two:
Expand|Select|Wrap|Line Numbers
  1. SELECT Output.ID, qryPossibleStartSlots.PossibleStartSlot, qryPossibleEndSlots.PossibleEndSlot
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM ([Output] LEFT JOIN RoomlessSections ON Output.Sec=RoomlessSections.Section) LEFT JOIN Roomless ON Output.Location=Roomless.OffcLoc
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. Slot  SlotStart    SlotEnd    Term
  2. 1      8:30 AM     9:20 AM    FW
  3. 2      9:30 AM    10:20 AM    FW
  4. 3     10:30 AM    11:20 AM    FW
  5. 4      8:30 AM     9:45 AM    FW
  6. 5     10:00 AM    11:15 AM    FW
  7. 6     11:30 AM    12:20 PM    FW
  8. 7     12:30 PM     1:20 PM    FW
  9. 8      1:30 PM     2:20 PM    FW
  10. 9     11:30 AM    12:45 PM    FW
  11. 10     1:00 PM     2:15 PM    FW
  12. 11     2:30 PM     3:45 PM    FW
  13. 12     4:00 PM     5:15 PM    FW
  14. 13     2:30 PM     3:45 PM    FW
  15. 14     4:00 PM     5:15 PM    FW
  16. 15     2:30 PM     5:15 PM    FW
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.
Jan 31 '12 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub SlotChecker()
  2. On Error GoTo Err_SlotChecker
  3.  
  4. Dim strSQL As String
  5. Dim fileName As String
  6.  
  7. DoCmd.SetWarnings False
  8.  
  9. If Me!fileName = "" Then
  10. MsgBox "file name is blank"
  11. Call RunQryCurrVerAnalysis_Click
  12. End If
  13.  
  14. ' make a new Output Table with Rooms (tOWR)
  15. strSQL = "SELECT tO.* INTO tOWR " & _
  16.         "FROM [Output] AS [tO] " & _
  17.         "WHERE (tO.Sec Not In(SELECT [Section] " & _
  18.                       "FROM   [RoomlessSections])) " & _
  19.         "AND  (tO.Location Not In(SELECT [OffcLoc] " & _
  20.                            "FROM   [Roomless]));"
  21.  DoCmd.RunSQL strSQL
  22.  
  23. ' make a new Matched Sections Table
  24. strSQL = "SELECT tOWR.*, tOWR.ID AS MatchedID, tSS.Slot, tSE.Slot INTO tMatched " & _
  25.             "FROM (tOWR LEFT JOIN Slots AS tSS ON (tOWR.Start=tSS.SlotStart) AND " & _
  26.             "(tOWR.Term=tSS.TermUnique)) LEFT JOIN Slots AS tSE ON (tOWR.End=tSE.SlotEnd) AND " & _
  27.             "(tOWR.Term=tSE.TermUnique) " & _
  28.             "WHERE (tOWR.Start Is Not Null) AND (tOWR.End Is Not Null) AND (tSS.SlotStart Is Not Null) " & _
  29.             "And (tSE.SlotEnd Is Not Null) And (tSS.Slot=tSE.Slot);"
  30.  
  31. DoCmd.RunSQL strSQL
  32.  
  33. ' build and run slot check query
  34. strSQL = "SELECT tOWR.*, IIF([Start] Not In (SELECT [SlotStart] FROM [Slots]), " & _
  35.         "IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]), " & _
  36.           "'Start and End Times out of slot', 'Start Time out of slot'), " & _
  37.         "IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]), " & _
  38.           "'End Time out of slot', " & _
  39.           "IIF([tOWR].[ID] In (SELECT [MatchedID] FROM [tMatched]),'normal', 'cross-slot section'))) AS SlotCheck " & _
  40.           "INTO tSlotCheck " & _
  41.           "FROM tOWR; "
  42.  
  43. DoCmd.RunSQL strSQL
  44.  
  45. ' Call up the report
  46.     stDocName = "Slot Check"
  47.     DoCmd.OpenReport stDocName, acPreview
  48.  
  49.  
  50. Exit_SlotChecker:
  51.    DoCmd.SetWarnings True
  52.    DoCmd.Hourglass False
  53.     Exit Sub
  54.  
  55. Err_SlotChecker:
  56.     DoCmd.Hourglass False
  57.     MsgBox Err.Description
  58.     Resume Exit_SlotChecker
  59. End Sub
  60.  

Share this Question
Share on Google+
27 Replies


ADezii
Expert 5K+
P: 8,597
This can probably be attempted in Code, if you like I'll give it a shot when I hqqve some time.
Feb 2 '12 #2

P: 22
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.
Feb 2 '12 #3

ADezii
Expert 5K+
P: 8,597
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim MyDB As DAO.Database
  3. Dim rstSlots As DAO.Recordset
  4.  
  5. Me![lstSlots].RowSource = ""        'Clear ListBox
  6.  
  7. 'Need Values for both Start and End Times
  8. If IsNull(Me![txtStartTime]) Or IsNull(Me![txtEndTime]) Then Exit Sub
  9.  
  10. If DCount("*", "Slots", "[SlotStart] = #" & Me![txtStartTime] & "#") = 0 Then
  11.   MsgBox "No available Slots for the specified Start Time of [" & Format$(Me![txtStartTime], "Medium Time") & _
  12.          "]", vbExclamation, "No Matching Start Time"
  13.            Exit Sub
  14. Else    'Start Time Matches, but does the End Time?
  15.   If DCount("*", "Slots", "[SlotStart] = #" & Me![txtStartTime] & "# AND [SlotEnd] = #" & _
  16.      Me![txtEndTime] & "#") = 0 Then
  17.     MsgBox "No available Slots for the specified Start/End Time combination", vbExclamation, _
  18.            "No Matching Start/End Time Combination"
  19.              Exit Sub
  20.   Else
  21.     'There is at least 1 Slot available for the Start and End Times, what is it/are they?
  22.     strSQL = "SELECT * FROM Slots WHERE [SlotStart] = #" & Me![txtStartTime] & "# AND [SlotEnd] = #" & _
  23.               Me![txtEndTime] & "# ORDER BY [Slot]"
  24.     Set MyDB = CurrentDb
  25.     Set rstSlots = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  26.      Me![lstSlots].AddItem "Slot;Start;Stop;Term"
  27.       With rstSlots
  28.         Do While Not .EOF
  29.           Me![lstSlots].AddItem ![Slot] & ";" & ![SlotStart] & ";" & ![SlotEnd] & ";" & ![Term]
  30.             .MoveNext
  31.         Loop
  32.       End With
  33.   End If
  34. End If
  35.  
  36. rstSlots.Close
  37. 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.
Attached Files
File Type: zip Slots.zip (24.9 KB, 48 views)
Feb 2 '12 #4

Rabbit
Expert Mod 10K+
P: 12,315
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT o.*
  2. FROM Output AS o
  3. LEFT JOIN Slots AS s
  4. ON o.[Start] = s.SlotStart
  5. WHERE s.SlotStartIS NULL
  6.  
  7. UNION
  8.  
  9. SELECT o.*
  10. FROM Output AS o
  11. LEFT JOIN Slots AS s
  12. ON o.[End] = s.SlotEnd
  13. WHERE s.StartTime IS NULL
Feb 2 '12 #5

P: 22
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!
Feb 2 '12 #6

Rabbit
Expert Mod 10K+
P: 12,315
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.
Feb 2 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tS.SlotStart
  2.      , tS.SlotEnd
  3.      , tO.*
  4. FROM   [Output] AS [tO]
  5.        LEFT JOIN
  6.        [Slots] AS [tS]
  7.   ON   tO.Start = tS.SlotStart
  8. WHERE  (tS.SlotStart IS NULL)
  9.   AND  (tO.Sec Not In(SELECT [Section]
  10.                       FROM   [RoomlessSections]))
  11.   AND  (tO.Location Not In(SELECT [OffcLoc]
  12.                            FROM   [Roomless]))
  13.  
  14. UNION
  15.  
  16. SELECT tS.SlotStart
  17.      , tS.SlotEnd
  18.      , tO.*
  19. FROM   [Output] AS [tO]
  20.        LEFT JOIN
  21.        [Slots] AS [tS]
  22.   ON   tO.End = tS.SlotEnd
  23. WHERE  (tS.SlotEnd IS NULL)
  24.   AND  (tO.Sec Not In(SELECT [Section]
  25.                       FROM   [RoomlessSections]))
  26.   AND  (tO.Location Not In(SELECT [OffcLoc]
  27.                            FROM   [Roomless]))
Feb 2 '12 #8

P: 22
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?
Feb 2 '12 #9

Rabbit
Expert Mod 10K+
P: 12,315
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT o.*
  2. FROM Output AS o 
  3. LEFT JOIN Slots AS s 
  4. ON o.[Start] = s.SlotStart 
  5. WHERE s.SlotStart IS NULL 
  6.  
  7. UNION
  8.  
  9. SELECT o.* 
  10. FROM Output AS o 
  11. LEFT JOIN Slots AS s 
  12. ON o.[End] = s.SlotEnd 
  13. WHERE s.StartTime IS NULL  
  14.  
  15. UNION
  16.  
  17. SELECT o.* 
  18. FROM Output AS o 
  19. LEFT JOIN Slots AS s 
  20. ON o.[Start] = s.SlotStart AND o.[End] = s.SlotEnd 
  21. WHERE s.StartTime IS NULL 
Feb 2 '12 #10

NeoPa
Expert Mod 15k+
P: 31,186
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).

qryOutputNoRooms :
Expand|Select|Wrap|Line Numbers
  1. SELECT tO.*
  2. FROM   [Output] AS [tO]
  3. WHERE  (tO.Sec Not In(SELECT [Section]
  4.                       FROM   [RoomlessSections]))
  5.   AND  (tO.Location Not In(SELECT [OffcLoc]
  6.                            FROM   [Roomless]))
qryOutputMatch :
Expand|Select|Wrap|Line Numbers
  1. SELECT qONR.Start
  2.      , qONR.End
  3. FROM   [qryOutputNoRooms] AS [qONR]
  4.        INNER JOIN
  5.        [Slots] AS [tS]
  6.   ON   (qONR.Start = tS.SlotStart)
  7.  AND   (qONR.End = tS.SlotEnd)
qryResults :
Expand|Select|Wrap|Line Numbers
  1. SELECT qONR.*
  2.      , tSS.SlotStart
  3.      , tSE.SlotEnd
  4. FROM (([qryOutputNoRooms] AS [qONR]
  5.        LEFT JOIN
  6.        [qryOutputMatch] AS [qOM]
  7.   ON   (qONR.Start = qOM.Start)
  8.  AND   (qONR.End = qOM.End))
  9.        LEFT JOIN
  10.        [Slots] AS [tSS]
  11.   ON   (qONR.Start = tSS.SlotStart))
  12.        LEFT JOIN
  13.        [Slots] AS [tSE]
  14.   ON   (qONR.End = tSE.SlotEnd)
  15. WHERE  (qOM.Start IS NULL)
  16.   AND  (
  17.        ((tSS.SlotStart IS NULL) AND (tSE.SlotEnd IS NOT NULL))
  18.    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.
Feb 2 '12 #11

Rabbit
Expert Mod 10K+
P: 12,315
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT o.*  
  2. FROM Output AS o  
  3. LEFT JOIN Slots AS s  
  4. ON o.[Start] = s.SlotStart AND o.[End] = s.SlotEnd  
  5. WHERE s.SlotStart IS NULL
  6.    AND o.Sec Not In (SELECT Section FROM RoomlessSections) 
  7.    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.
Feb 2 '12 #12

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 2 '12 #13

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 2 '12 #14

Rabbit
Expert Mod 10K+
P: 12,315
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.
Feb 2 '12 #15

NeoPa
Expert Mod 15k+
P: 31,186
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
Feb 2 '12 #16

Rabbit
Expert Mod 10K+
P: 12,315
I think neither of our queries work. Because of the hitherto unforeseen 5d error. Where neither start nor end is in the table.
Expand|Select|Wrap|Line Numbers
  1. o.Start o.End type
  2. 01:00   02:00
  3. 03:00   04:00 a
  4. 05:00   06:00 b
  5. 01:00   06:00 c 
  6. 05:00   04:00 d
  7.  
  8. s.Start s.End
  9. 01:00   02:00
  10. 03:00   06:00
Feb 2 '12 #17

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 3 '12 #18

Rabbit
Expert Mod 10K+
P: 12,315
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.
Feb 3 '12 #19

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 3 '12 #20

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 
  2.        qONR.*
  3.      , tSS.SlotStart
  4.      , tSE.SlotEnd
  5. FROM (([qryOutputNoRooms] AS [qONR]
  6.        LEFT JOIN
  7.        [qryOutputMatch] AS [qOM]
  8.   ON   (qONR.Start = qOM.Start)
  9.  AND   (qONR.End = qOM.End))
  10.        LEFT JOIN
  11.        [Slots] AS [tSS]
  12.   ON   (qONR.Start = tSS.SlotStart))
  13.        LEFT JOIN
  14.        [Slots] AS [tSE]
  15.   ON   (qONR.End = tSE.SlotEnd)
  16. WHERE  (qOM.Start IS NULL)
[qryOutputNoRooms] 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.
Feb 3 '12 #21

Rabbit
Expert Mod 10K+
P: 12,315
My bad, you are absolutely right. I just had to work it out on paper to see it.
Feb 3 '12 #22

NeoPa
Expert Mod 15k+
P: 31,186
: - )
Feb 3 '12 #23

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT qOWR.*, 
  2. IIF([Start] Not In (SELECT [SlotStart] FROM [Slots]),
  3.      IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]),
  4.           "Start and End Time out of slot",
  5.           "Start Time out of slot"
  6.           ),
  7.      IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]),
  8.           "End Time out of slot",
  9.           IIF([qOWR].[ID] In (SELECT [MatchedID] FROM [qryMatched]),"normal", "cross-slot section")
  10.           )
  11. ) AS SlotCheck
  12. FROM [qryOutputWithRooms] AS [qOWR];
qryMatched:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. qOWR.*, qOWR.ID AS MatchedID
  3. , tSS.Slot
  4. , tSE.Slot
  5. FROM ([qryOutputWithRooms] AS [qOWR]
  6. LEFT JOIN
  7. [Slots] AS [tSS]
  8. ON (qOWR.Start = tSS.SlotStart AND qOWR.Term = tSS.TermUnique))
  9. LEFT JOIN
  10. [Slots] AS [tSE]
  11. ON (qOWR.End = tSE.SlotEnd AND qOWR.Term = tSE.TermUnique)
  12. 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 qryOutputWithRooms:
Expand|Select|Wrap|Line Numbers
  1. SELECT tO.*
  2. FROM [Output] AS [tO]
  3. WHERE (tO.Sec Not In(SELECT [Section] FROM   [RoomlessSections]))
  4.   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. qryOutputWithRooms 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
Expand|Select|Wrap|Line Numbers
  1. SELECT qOWR.*
  2. , IIF([tSS].[SlotStart] = "", "Start Time out of Slot","") AS Check
  3. FROM [qryOutputWithRooms] AS [qOWR]
  4. LEFT JOIN
  5. [Slots] AS [tSS]
  6. ON (qOWR.Start = tSS.SlotStart)
  7.  
  8. UNION
  9.  
  10. SELECT qOWR.*
  11. , IIF([tSE].[SlotEnd] = "", "End Time out of Slot","") AS Check
  12. FROM [qryOutputWithRooms] AS [qOWR]
  13. LEFT JOIN
  14. [Slots] AS [tSE]
  15. ON (qOWR.End = tSE.SlotEnd);
  16.  
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.
Feb 6 '12 #24

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub SlotChecker()
  2. On Error GoTo Err_SlotChecker
  3.  
  4. Dim strSQL As String
  5. Dim fileName As String
  6.  
  7. DoCmd.SetWarnings False
  8.  
  9. If Me!fileName = "" Then
  10. MsgBox "file name is blank"
  11. Call RunQryCurrVerAnalysis_Click
  12. End If
  13.  
  14. ' make a new Output Table with Rooms (tOWR)
  15. strSQL = "SELECT tO.* INTO tOWR " & _
  16.         "FROM [Output] AS [tO] " & _
  17.         "WHERE (tO.Sec Not In(SELECT [Section] " & _
  18.                       "FROM   [RoomlessSections])) " & _
  19.         "AND  (tO.Location Not In(SELECT [OffcLoc] " & _
  20.                            "FROM   [Roomless]));"
  21.  DoCmd.RunSQL strSQL
  22.  
  23. ' make a new Matched Sections Table
  24. strSQL = "SELECT tOWR.*, tOWR.ID AS MatchedID, tSS.Slot, tSE.Slot INTO tMatched " & _
  25.             "FROM (tOWR LEFT JOIN Slots AS tSS ON (tOWR.Start=tSS.SlotStart) AND " & _
  26.             "(tOWR.Term=tSS.TermUnique)) LEFT JOIN Slots AS tSE ON (tOWR.End=tSE.SlotEnd) AND " & _
  27.             "(tOWR.Term=tSE.TermUnique) " & _
  28.             "WHERE (tOWR.Start Is Not Null) AND (tOWR.End Is Not Null) AND (tSS.SlotStart Is Not Null) " & _
  29.             "And (tSE.SlotEnd Is Not Null) And (tSS.Slot=tSE.Slot);"
  30.  
  31. DoCmd.RunSQL strSQL
  32.  
  33. ' build and run slot check query
  34. strSQL = "SELECT tOWR.*, IIF([Start] Not In (SELECT [SlotStart] FROM [Slots]), " & _
  35.         "IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]), " & _
  36.           "'Start and End Times out of slot', 'Start Time out of slot'), " & _
  37.         "IIF([End] Not In (SELECT [SlotEnd] FROM [Slots]), " & _
  38.           "'End Time out of slot', " & _
  39.           "IIF([tOWR].[ID] In (SELECT [MatchedID] FROM [tMatched]),'normal', 'cross-slot section'))) AS SlotCheck " & _
  40.           "INTO tSlotCheck " & _
  41.           "FROM tOWR; "
  42.  
  43. DoCmd.RunSQL strSQL
  44.  
  45. ' Call up the report
  46.     stDocName = "Slot Check"
  47.     DoCmd.OpenReport stDocName, acPreview
  48.  
  49.  
  50. Exit_SlotChecker:
  51.    DoCmd.SetWarnings True
  52.    DoCmd.Hourglass False
  53.     Exit Sub
  54.  
  55. Err_SlotChecker:
  56.     DoCmd.Hourglass False
  57.     MsgBox Err.Description
  58.     Resume Exit_SlotChecker
  59. End Sub
  60.  
Feb 6 '12 #25

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 7 '12 #26

P: 22
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.
Feb 7 '12 #27

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 7 '12 #28

Post your reply

Sign in to post your reply or Sign up for a free account.