HiVJ,
Thanks for enlightening me further.
Accessis quite a different animal than Word or Excel.Accesstables have
Columns with different "Headers", but does not have Headers in rows like Word
tables or Excel. (AlthoughAccesscan produce that effect with a Form's PivotTableview or in crosstab queries that is another subject entirely.) You
should not try to use that format in your tables if you want your Database to
work well.
Unlike Word or Excel, inAccess, most of thetimeyou rarely look at
tables--they are not set up for that. You use queries, forms, and reports to
"see" your data.
AccessTables are designed to store your data. Forms, Queries, and Reports
are designed to display your data in the format you desire. Accesstables
are not flexible enough to do what you want. Queries, forms, and reports can
produce some very impressive displays and arrangements of data. There is no
other database that has the ease of use and capabilities thatAccesshas to
produce and incredible variety of user frindly information.
You can create your tables, then create forms to enter and display that
information to you. And you can arrange the information in any way you want!
If you want to see the days on the side and the Periods on top, I recommend
you create a form to do that.Accesshas a powerful Form Wizard that will add
all the fields you choose to the form. Then, if you want to change that
arrangement, it is as simple as clicking on a text box and dragging it to the
desired location.
Below is a crudely drawn example of what you can do with a Form or a Report.
________
This represents a texbox where you enter and see your data. |________|. The
little squares are checkboxes. The words are on labels which can be made
transparent so you only see the letters.
You can create a Form or Report that lets you see all the data at once.
You can add search combo boxes that will instantly take you to the
information you want to see.
FORM SAMPLE:
Find a Teacher Find a Batch
___________________ ___________________
|___________________| |___________________|
BatchName Teacher
___________________ ___________________
|___________________| |___________________|
PERIODS: I II III IV V
_ _ _ _ _
Monday |_| |_| |_| |_| |_|
_ _ _ _ _
Tuesday |_| |_| |_| |_| |_|
_ _ _ _ _
Wednesday |_| |_| |_| |_| |_|
_ _ _ _ _
Thursday |_| |_| |_| |_| |_|
_ _ _ _ _
Friday |_| |_| |_| |_| |_|
___________________________________________________
You don't have to name the labels exactly the same as the fields in yourtable. This makes for a neat looking form.
___________________________________________________
Another Form Sample:
Find a Teacher Find a Batch
______________________ ______________________
|______________________| |______________________|
BatchName Teacher
______________________ ______________________
|______________________| |______________________|
Period Venue Subject
____ _____________ ______________________
|____| |_____________| |______________________|
Mon Tues Wed Thurs Fri
_ _ _ _ _
|_| |_| |_| |_| |_|
___________________________________________________
I hope this helps you understand why I am recommending this way of designing
your tables.
After seeing your additional information one way you can do it is to create
atablefor Teachers.
Since Teachers will (I assume) change Batches from semester to semester or
year to year, you can create atablefor teachers and relate it to the
Batchestable.
Your Tables might look like this in datasheet view:
tblTeacherID:
TeacherID LastName FirstName MI Suffix
1 Smith Sally J
2 Jones Fred Jr.
3 Holmes Sarah W
4 Jenkins Margaret
tblBatchID:
BatchID TeacherID BatchName ScheduleID BatchSubject BatchVenue
1 3 BatchA 2 Basic Math Room 46
2 1 BatchB 1 English 101 Conference Room
3 2 BatchC 3 Education 201 Room 21
4 4 BatchD 4 Sociology 211 Room 14
tblSchedule:
ScheduleID BatchID BMon BTues BWed BThurs BFri BatchPeriod
1 2 No Yes No Yes No 2
2 1 Yes No Yes No Yes 1
3 3 No No No Yes Yes 4
4 4 No Yes Yes No No 3
TABLENAME FIELDNAME DATATYPE MY NOTES
tblTeachers
TeacherID (Autonumber) --Set This field to be Primary Key
If a Teacher has a unique School ID
Number you can use that and
set the data type to (Number)
LastName (Text) --Having seperate fields for LastName
and FirstName will enable
you to sort by student name easily.
FirstName (Text)
MiddleName (Text) --or MI (this field is optional)
Suffix (Text) --like Jr., Sr., III, etc.)
tblBatches
BatchID (Autonumber) --Set This field to be the Primary Key
TeacherID (Number) --This field is called a "foreign Key"
and relates thistableto tblTeachers
BatchName (Text)
ScheduleID (Number) --This field is the "foreign Key"
to relate totabletblBatches
BatchSubject (Text)
BatchVenue (Text)
tblSchedule
ScheduleID (Autonumber) --Set This field to be Primary Key
BatchID (Number) --This field is called a "foreign Key"
and relates thistableto tblBatches
BMon (Yes/No) --The "B" is to avoidAccesserrors
BTues (Yes/No)
BWed (Yes/No)
BThurs (Yes/No)
BFri (Yes/No)
BatchPeriod (Number)
Thetablerelationships work like this:
1) The Primary Key Field of tblTeachers (TeacherID) will relate
to the "Foreign" TeacherID Field in tblBatches.
1) The Primary Key Field of tblSchedule which is ScheduleID
will relate to the "Foreign" ScheduleID Field in tblBatches.
Dear Hunter57,
Thankyou for the reply. I have some more information to share with you
on my problem. Sorry that the information from my side is coming out
in chunks, but only after reading your replies I have been able to
'see' my problem from different angles, and realise that it can be
seen and interpreted differently by different people. So, my apologies
for not being able to provide all information in a single go.
As I mentioned earlier, I have a timetable in the form of a table (in
MSWord, not in Access), for each of the groups or batches. The groups
or batches are recognised as a whole group, and individual identity of
students is not of significance. Each such table consists of days of
the week (Mon-Fri) on the leftmost column of a table and the period
(hour) (say I, II,....upto VIII) on the top row of the table. In each
cell of this table, there is the name of the subject (such as Maths,
Physics etc), and other details such as venue (name of lecture room,
such as L1, L2 etc), name of the teacher etc.
I wish to add to this information that: the information regarding the
venue and the teacher name in each cell of a particular batch can be
different within each batch, that is, within the timetable of each
batch, we may have different teachers for different (or even same)
subject (on different days of the week), and within each batch there
may be different venues for different periods. In other words, it is
not like this that a particular teacher is responsible for the whole
batch, nor a particular venue is assigned dedicatedly to a particular
batch for all its classes to be held in.
I want to create a database using this information, so that I can view
the information in Days vs Period format such as
(1) in the form of a table (Days vs Period) for each Group, with
multiple information in each cell, such as subject, venue, teacher
initials.
(2) the timetable or occupancy of each Lecture room i.e. venue (over
multiple groups) again in the form of Days vs Period, with multiple
information such as teacher name and subject
(3) teacher-wise, (again in the form of Days vs Period, giving the
information of subject, venue etc.
I don't know if I am asking for too much from Access. But by this
time, I have at least got an idea that the problem is not an easy one.
It is not easy for me to understand regarding the Forms you mentioned
in your last reply, but I am sure the reply will be useful later when
I get along better with Access and re-read your reply.
Can you please tell me in the first place what fields I should take in
the Access table, and how many table (one for each batch?), so that I
can fill in the information and play with the forms and reports.
I can send the time table of a batch or two made in MSword to you via
email, if agreeable to you, for you to have a look and suggest.
Thankyou once again for your interest and help.
), for each of these groups or batches. Each such table