Too many queries

[Somecallmejosh]

Yes, I realize that. Here is the structure:

In an effort to avoid communication for the person
entering information into the scheduling form I chose to
enter the following per day of week for scheduling of the
studios...

Partial list of table in question...

ClassName - lookup from Class table
Instructor - Lookup from Instructor table
Monday - yes/no
MondayClass1time - date/time
MondayClass1Studio - ComboBox -> Studio1, Studio2, Studio3
MondayClass2time -
MondayClass2Studio -
MondayClass3time -
MondayClass3Studio -

and so on for Tuesday, Wednesday, Thursday, and
Saturday. Any given class by an instructor can be taught
multiple times, and multiple studios, per day. This is
the reason I chose to set up the table this way, as
opposed to setting up drop down boxes with unyielding
variability. Of course, there probably is an easier way
that I have overlooked.

For example... Teacher "a" may teach Jazz on Monday in
studio 1 at 16:00, in studio 2 at 17:00, and studio 3 at
18:00. Whereas teacher "b" may be teaching tap in studio
2 at 16:00, Studio 1 at 17:00, etc. Is there a better
way I could have set up the table to accommodate for such
variability in scheduling? Yes, I realize the scheduling
is a little "off the mark", if you will, but it's what I
have to work with.

Thanks a bunch.
Josh

 

[John Vinson]

Yes, I realize that. Here is the structure:

In an effort to avoid communication for the person
entering information into the scheduling form I chose to
enter the following per day of week for scheduling of the
studios...

Partial list of table in question...

ClassName - lookup from Class table
Instructor - Lookup from Instructor table
Monday - yes/no
MondayClass1time - date/time
MondayClass1Studio - ComboBox -> Studio1, Studio2, Studio3
MondayClass2time -
MondayClass2Studio -
MondayClass3time -
MondayClass3Studio -

and so on for Tuesday, Wednesday, Thursday, and Saturday.

Is there a better
way I could have set up the table to accommodate for such
variability in scheduling? Yes, I realize the scheduling
is a little "off the mark", if you will, but it's what I
have to work with.

Yes. There is a MUCH better way; it's using Access as it was designed,
as a relational database instead of a spreadsheet.

You've made the very easy error of confusing data PRESENTATION - a
spreadsheet is natural for the user - with data STORAGE.

First off, I would avoid EVER using Lookup Fields. They're far more
trouble than benefit; see http://www.mvps.org/access/lookupfields.htm
for a critique. Use lookup *tables* obviously, and use Combo Boxes
("lookups" if you wish) on Forms - but not in Tables. I'd structure my
class session table as:

ClassSessions
*ClassID << link to Class table
*SectionNo << 1, 2, 3
*DayOfWeek << either weekday name or, probably better, 1 for Sunday
- 7 for Saturday
ClassTime Date/Time
InstructorID << link to Instructor table
ClassLocation << link to table of Studios, or you could just use a
List Of Values combo box on your Form to enter it. I'd use a table
though.


*These three fields would be a joint Primary Key for ClassSessions.

You might even have two tables - one for a "Section" (the class taught
by Ms. Ramirez that meets M W F at 10:00) related one to many to a
table of "Sessions" (the Friday 10/29 10:00am meeting of the class).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps