Checking for Similar Values Contained in Different Records in Another Field

[mattreed75]

I've been searching for a solution, but can't really describe it well enough to find a Google result....



I have a DB I use for an academy registration. I need to determine if registrants signed up for courses that overlap - some do it to make sure they get into a desired class, some are just...well, you know.

Among several others, I have a tblRegistrants (which I don't think I need here - it contains info about the person, including pkPersonID), tblRegistrations (which contains info about the particular course they signed up for, including pkRegID, fkClassID, and fkPersonID), and tblClasses (lookup table with pkClassID, as well as an [Overlap] field (long text) listing the courses that overlap with that class. qryClassOverlaps is simply a left join of tblRegistrations to tblClasses on pkClassID

So I have a query that returns each registration with the overlap field.

How do I write another query to check to see if the contents of fkClassID are found in Overlaps in any other records within qryClassOverlaps where fkPersonID is the same?

Thanks in advance

[madpiet]

Do you have a small database with a few classes (some that overlap) handy that you could share? We don't want/need anything confidential, so remove that. but basically, checking for overlap is super fun. I am crazy rusty on this because I haven't done it a lot since 25 years ago when I had to figure it out... But I think it boils down to something like this:

Show me all classes where both of these are True:
1. Proposed class ends before existing one(s) start.
2. Proposed class starts after existing one(s) end.

( I remember doing this on paper... with a number line (of hours)... sounds silly until you try to figure this kind of thing out).

[mattreed75]

I do not, but I could probably make one. The one I have is pretty big. I could pull out the appropriate tables and delete any personal data, because that data is not important for this to work... I'll need a little time...

[madpiet]

Usually the easiest way is to create a new database, and then import the pieces you need. Then test once you think you have them all. (If you don't, you'll get errors in your code or queries, and you can go back and import more objects). The trick is to whittle it down to just what's necessary. (A bit like writing a resume, LOL)

[CJ_London]

Define ‘overlap’

are these week long courses, some starting on a Monday and others on a Thursday?

or are they over say 3 months with 2 hourly lessons three times a week?

whatever it is you will need specific start and end dates and perhaps times for each course/lesson

something else?

[mattreed75]

That's what I did. The only thing needed should be tables and query I mentioned, from which I removed all names, emails, etc. Thing is, it's still 13 MB. I'm afraid if I start deleting records, there will be less chances of finding overlaps - I've already removed three...

[mattreed75]

So, I have this, CJ, I just thought it would be easier to search for the Class ID in another field. I have Start and End date fields, but I didn't fill them in this year. The start and end dates and times are actually included in the "LongName" field, because those are the values that come in the table from the registration website.

It is a one week academy with about 20 different offerings. Courses start at either 0800 or 1300, and end at either 1200 or 1700.

[CJ_London]

So where is the overlap?

two courses with the same start/end times?

you will need to split out the start end times from the description

[mattreed75]

So I remembered I had an attachment field in tblClasses with pictures representing the courses on their nametags. Deleted that and some other stuff, and cut the registrants down to 200, and I got it under 500 KB...
SampleAcademy.accdbSampleAcademy.accdb

[mattreed75]

So where is the overlap?

two courses with the same start/end times?

you will need to split out the start end times from the description

The overlap is if a registrant signed up for two classes that run simultaneously.

So let's say I enter the start and end times and dates - what is the next step? That's what I'm looking for. I have about 350 people registered about 400 times. Most of them are for one class, but some are taking multiple courses, which they can as long as they don't overlap. I need to find the records with overlapping courses. To add to the confusion a little, people can be signed up for Staff or Instructor, as well as take a course, but I'm not concerned about overlaps with Staff or Instructor, just between courses. I can't figure out how to compare different fields in different records that share the contents of a third field.

[madpiet]

If there are only two time slots, this is easy. If not, it's a pain. You'd just group by Term (or whatever), and then WeekDay, and then TimeSlot and get a count and filter (HAVING COUNT(*)>1) Oh, since you have the overlapping courses in a column, you could use INSTR() to test. or split them out to a child table and then use EXISTS and compare to the student's current courses.

[mattreed75]

If there are only two time slots, this is easy. If not, it's a pain. You'd just group by Term (or whatever), and then WeekDay, and then TimeSlot and get a count and filter (HAVING COUNT(*)>1) Oh, since you have the overlapping courses in a column, you could use INSTR() to test. or split them out to a child table and then use EXISTS and compare to the student's current courses.

I'm not sure I follow, but the time slots are many...

[mattreed75]

If there are only two time slots, this is easy. If not, it's a pain. You'd just group by Term (or whatever), and then WeekDay, and then TimeSlot and get a count and filter (HAVING COUNT(*)>1) Oh, since you have the overlapping courses in a column, you could use INSTR() to test. or split them out to a child table and then use EXISTS and compare to the student's current courses.

Ahhh! I just figured out what you meant - yes, I think that will work! Thank you!

[madpiet]

The Project-ish grid is super helpful! I drew something like that when I had to sort this out like 25 years ago. =)

Okay, the definition of an "overlap" between two courses is if the StartDate for one is between the StartDate and EndDate of another. To see that, you could create a deliberate Cartesian Product query (where you put two copies of tblClasses into your query grid and don't join them). Then you check to see if A.StartDate > B.StartDate AND A.EndDate<=B.EndDate. (Well, there are a couple more tests, but if any one is true, it conflicts, so you OR all the tests together.

Since your set of classes is static, you could just do that and return the pairs of classes that overlap (that's somehow magically in your table!). if you did it as (Course, OverlappingCourse), you'd just look for both Course and OverlappingCourse to show up in the same Schedule.

Don't want to speculate further right now... too braindead to be of a lot of help, so more later when I'm more awake.

[mattreed75]

The Project-ish grid is super helpful! I drew something like that when I had to sort this out like 25 years ago. =)

Okay, the definition of an "overlap" between two courses is if the StartDate for one is between the StartDate and EndDate of another. To see that, you could create a deliberate Cartesian Product query (where you put two copies of tblClasses into your query grid and don't join them). Then you check to see if A.StartDate > B.StartDate AND A.EndDate<=B.EndDate. (Well, there are a couple more tests, but if any one is true, it conflicts, so you OR all the tests together.

Since your set of classes is static, you could just do that and return the pairs of classes that overlap (that's somehow magically in your table!). if you did it as (Course, OverlappingCourse), you'd just look for both Course and OverlappingCourse to show up in the same Schedule.

Don't want to speculate further right now... too braindead to be of a lot of help, so more later when I'm more awake.

Thanks again. Your first recommendation reminded me that I already had a query that placed X's in blocks assigned to morning and afternoon sessions based on the class registration. So I ended up running another query on that one, grouped on the persons name, COUNT pkPersonID, where [Tue AM] = "X". Then I just changed the test field for each one to check each day. That gave me exactly what I needed.