tables

[Loi]

Hi,

Please show me how to set up a table so that each student
can take rotated classes at different dates.


Also I would like the report will show Dates (6/10/04,
7/26/04, 8/25/04 .....)on the top, and the detail will
show classes (ICU, Surgery1, Surgery 2, IM). The big
problem is that I like no class is duplicate in horizontal
and vertical ways.

For example:

There are 10 classes: ICU, Surgery1, Surgery 2, IM, CD,....

For student 1

In column: 6/10/04 7/26/04 8/25/04 9/12/04
rows : ICU Surgery1 Surgery2 IM

Student2, schedule shoul be...
In colum: 6/10/04 7/26/04 8/25/04 9/12/04
surgry1 Surgery2 IM ICU.


I would like to have the report looks like below.
Student1 6/10/04 7/26/04 8/25/04 9/12/04
ICU Surgery1 Surgery2 IM
Student2 surgry1 Surgery2 IM ICU


Thank you very much
Loi

 

[John Vinson]

Hi,

Please show me how to set up a table so that each student
can take rotated classes at different dates.


Also I would like the report will show Dates (6/10/04,
7/26/04, 8/25/04 .....)on the top, and the detail will
show classes (ICU, Surgery1, Surgery 2, IM). The big
problem is that I like no class is duplicate in horizontal
and vertical ways.

For example:

There are 10 classes: ICU, Surgery1, Surgery 2, IM, CD,....

For student 1

In column: 6/10/04 7/26/04 8/25/04 9/12/04
rows : ICU Surgery1 Surgery2 IM

Student2, schedule shoul be...
In colum: 6/10/04 7/26/04 8/25/04 9/12/04
surgry1 Surgery2 IM ICU.


I would like to have the report looks like below.
Student1 6/10/04 7/26/04 8/25/04 9/12/04
ICU Surgery1 Surgery2 IM
Student2 surgry1 Surgery2 IM ICU

A properly normalized design would NOT have dates as field names.
<shudder!>

Instead, have four tables:

Students
StudentID <Primary Key>
LastName
FirstName
<other bio data>

Classes
ClassName <Primary Key>

ClassSessions
ClassName
ClassDate Date/Time <joint two-field primary key>

Enrollment
StudentID
ClassName
SessionDate

You could then create a Crosstab Query to get the desired report
appearance.

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