Dear Paul:
It is almost always better to have all the rows in one table. For
this problem, that rule still stands. As a temporary solution, you
should begin with a UNION ALL query:
SELECT [Instructor Name], Instrument, Location,
"Basic" AS Course, [Lesson Time]
FROM BasicTable
UNION ALL
SELECT [Instructor Name], Instrument, Location,
"Advance 1" AS Course, [Lesson Time]
FROM BasicTable
In the above, substitute the actual names of the tables you have, and
repeat the last 4 lines (from "UNION ALL" forward) for each additional
table. Be sure to put in a different Course name for each as
appropriate.
Although your proposed results indicate you want to see the Student
and the Class #, you haven't indicated how this would be obtained.
I'm also confused about Instrument and Location. Could an instructor
be with two different students on different instruments
simultaneously, or at two different locations simultaneously?
What I'm suggesting is that this is part of the data in the matrix,
rather than at the top of the page. Would this organization make more
sense:
Instructor Name
Mon Tues Wed
1 Student
Basic
Class #
Instrument
Location
2 Student Student
Basic Advance 1
Class # Class #
Instrument Instrument
Location Location
If an instructor only teaches one instrument at one location, then it
is an error in data normalization to store that in the table with
Lesson Time. You would be storing this invariant information many,
many times for each instructor.
If the instructors teach more than one instrument or at more than one
location during the week, then it is appropriate to store the
information as you have shown, but it would not be correct to show
that in the heading of your report, as it may vary from day to day and
time to time.
You need to change one of the other of these things.
After we get these things understood and fixed, we can proceed to the
solution you'll need. I think we can just break up the Lesson Time
into day of the week and time of day and proceed to making a crosstab
of that. How had you planned to derive the day of the week and the
period number from your column Lesson Time? What does that look like
now?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Tom,
Thank you very much for your response.
Here is the scenario:
We run ten courses at the school. Each course has its own
table.
One common factor for ALL courses is the Instrumental
Instructor. Each instructor has six periods per day when
they can give a lesson to one student.
The common columns in each table are:
Instrument/Instructor/Location/Lesson Time.
SOOOO...
I cannot "double book" an instructor for any given period
and my ultimate goal is to create a report on a 5X8 index
card with the instructor's weekly schedule.
An example:
Instructor Name Instrument Location
Mon Tues Wed Thu Fri
1 Student
Basic
Class #
2 Student Student
Basic Advance 1
Class # Class #
3 Student
Basic
Class #
4 Student
Advance 2
Class #
5
6
ETC...
Does this informantion help?
Paul
-----Original Message-----
Dear Paul:
It would probably be better to put the students from the various
"levels of study" tables into one table, adding a column to describe
the "level of study" from which each row originates. Your suggestion
to use a UNION query is a viable alternative. I would recommend you
begin the design of this by identifying the common columns from the
various table sources that will be needed in the result. You should
probably add a column that I'd call [Source] to this query. In it,
you can identify each of the various student tables from which the
data originated.
Not knowing what columns you will use from the various student tables,
I can't give you much of an example. If you need one, give me some
sample information about the UNION query columns you expect to create.
Each table must contribute columns to the union, and must have the
same number of columns in the same sequence. They must match in
datatype, or at least be compatible. If a table doesn't have one of
the columns you need, you can insert a NULL column coming from that
particular source table.
I can't tell if I'm being to simple or too complex for you. Maybe we
can get a dialog started from this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Hello and thanks in advance.
I have a database with SEVERAL tables linked together.
(using Office 2000). One table is an instructor table.
Each instructor listed on the table has students from many
different levels of study and each student is assigned one
hour per week with that instructor. I need to create a
query/report that shows the instructor schedule from every
source table available.
Can anyone assist?
Thanks
Paul
.
