Access 97 Query Question

[OBarry]

I'm not sure if this is going to make sense but I'm going to ask anyway...

I have a table with the following fields: Date, Time1, Time2, ClassCodeA, ClassCodeB, ClassCodeC, ClassCodeD, ClassCodeE, ClassCodeF

The classes (each ClassCode field) can be scheduled at either 09:45am (Time1 field) or 11:15am (Time2 field), every Tuesday through Friday (the Dates are already entered in the table for every Tuesday through Friday for a year. In the Date field). If a class has been scheduled the Teacher's ID number is entered in the appropriate ClassCode field.

What I'm trying to accomplish is... Usually when a teacher calls they like to schedule two different classes on the same day. For example: a teacher calls and asks "What is the first available date that I can schedule ClassCodeB and ClassCodeF?" It doesn't matter which class is scheduled first or second as long as they can take both classes on the same day. In a query I have it look for the two ClassCodes the teacher wants (If ClassCodeB and ClassCodeF IS NULL) but I don't know how to make it find the first available Date where one class is at 09:45am and the other at 11:15am.

At this point I will make any changes to the table or whatever that will help make this work.

Thanks for any suggestions or help in advance.

 

[JoeMiller]

Are B and F the only class codes the teachers ask about? Or are you just looking to find a way to find a day where two codes (be it A, B, C, D, or F) are open? And one other question, can a teacher do A, B as groups or are the times overlapping so that they can't do A, B but they can do A, C. I guess I'm trying to find out if there are groupings of codes that they should be doing.

Joe Miller
joe.miller@flotech.net

 

[OBarry]

Hi Joe,

All 6 classes are available at 09:45am and 11:15am, every Tuesday through Friday. The two classes a teacher might ask for could be a combination of any of the ClassCodes.

Example: Say a teacher asks for classes A and B, if class A is already booked for 09:45am but is available at 11:15am then class B has to available at 09:45am.

Thanks for your help.

 

[JoeMiller]

I'm sorry to dumb this down, but I have to in order to make sure I'm understanding correctly. The comments in parenthesis either ask questions or define my assumptions. I have two scenarios, they are similar but different! ;-)

SCENARIO 1
A teacher calls in and says:

"I want to sign up for two classes. I can only have my first class at 9:45 am and my second class at 11:15 am."

Now the person on the other end says:

"Ok, we have 5 classes (A thru F) and you can take ANY class in either time slot. Let me see what day I have ANY two DIFFERENT (are they always different, or can they be like?) classes open for your desired day."


SCENARIO 2
A teacher calls in and says:

"I want to sign up for classes A and C (any class combo possible except like, ie: A and A, C and C). I can only have my first class at 9:45 am and my second class at 11:15 am."

Now the person on the other end says:

"Ok, we have classes A and C available in two different time slots on MM/DD/YY. Would you like to sign up for them?"

End Scenarios

Hopefully you can see my ambivalence in knowing how to proceed after reading my scenarios.

Joe Miller
joe.miller@flotech.net

 

[OBarry]

Hi Joe,

I really appreciate your help!!

Scenario 2 is what we usually get, the teacher will always give us the two classes they want (always two different classes and never the same class) so we need to find the first day both classes are available at the two different times, and it doesn't matter which class is taken 9:45am or 11:15am as long as it's on the same day.

Thanks again!

 

[JoeMiller]

OBarry,

I have come up with a solution to your problem, but it is starkly different than your implementation. If you provide me with an email address I will forward you a database I whipped up to provide the skeleton of what you are looking for.
Joe Miller
joe.miller@flotech.net