sugg for relation between tables

[Alpay Eno]

Hello all, I'd appreciate some pointers here... I've got
a school scheduling app working where my ROWS are Periods
1-9 and my COLUMNS are grades 5a, 5b, 6a, 6b, etc to 8b.

This table contains subject names (reading, math, etc)
and I'd like to have a seperate table associating teacher
names to subjects (some teachers teach multiple
subjects). Whats the best way to design this? I
ultimately want to do a query where only the subjects a
specific teacher is associated with shows on the grid
(schedules to be printed for each teacher).

Thanks all...
Alpay Eno

 

[Jeff Boyce]

From your description, I'm wondering if you might have imported data from
Excel (or another spreadsheet). I ask because of the use of "repeating"
column names (i.e., the columns represent variations on a single theme --
grade level in your case).

Given what I can understand, my first suggestion would be to step back from
the table(s) and review the topic of normalization. While normalizing your
data isn't strictly necessary in order to use Access, you'll find a
consensus opinion in this newsgroup (tablesdbdesign) that failing to
normalize your data structure will cause you headaches.

Headaches from trying to "relate" tables; from trying to build queries;
and forms; and reports!

Or perhaps I've misinterpreted your description...?

Good luck

Jeff Boyce
<Access MVP>

 

[Alpay Eno]

Thanks for the reply, I'm not so sure I understand though
(normalization)... The data is not from Excel, its from a
good ol' fashion pen and paper (and whiteout) method
which has been very frustrating for the principal.

The variations of the column represent two classes per
grade, classes A and B in grades 5 through 8. I basically
built the table as simple as her "paper" schedules looked
as I did not anticipate future steps. Now that I need to
isolate subjects for individual teachers and I'm not sure
of the best way to do this. can be seen at
www.smvisuals.com/bronxprep.asp

right now my query is something like
SELECT * FROM schedule WHERE [period]='1'
and I use that in a FOR/Next loop

I basically want to query by teacher, which does not
exist in the table yet. Thanks so much for the help!

 

[Alpay Eno]

ok, I did some reading (normalization, a necessary step).
So I think I'm on the right track but still don't know
how to structure the query...

I have a table now called schedule with a pattern like:
period class5a class5b class6a class6b
1 reading math science music
2 math reading music science

what if I created a table called teachers like
subject name
reading smith
math rogers
science reeves
music smith

I want to do a query of data from the schedule table
based only on a teachers name (which is in the teachers
table). I'm looking at JOINS but I'm thoroughly
confused...