table set up

[Guest]

I am planning a database to control lesson courses I provide.
I want to store details of students, available course topics they can sign
up to, location of course and details specific courses eg: student names
start time location date.
I have figured I will need:

Student table - name, tutor, year, courses completed,etc.
Topics Table - available courses one field
Location Table - location of classrooms 1 field
Course table - details of course several fields; date, location, student
name

I am not sure how to relate these tables.
I would also like to to print a report of courses done by each student i.e
date ,topic, grade.Not sure how bwst to store this in the table.
When it works I would like to let students sign them selves on to a specific
course using a form over the intranet. This would need to be protected to
prevent others deleting information after a student has signed up. I think a
data access page would do this but need to accomodate it now.
Any advice on setting this up or where I may be making a design error would
be greatly appreciated.
Thanks
Kit

 

[Ed Robichaud]

Kit - there are templates available for class/event planning databases, but
based on your starting point, I would recommend a data dictionary something
like that below:

tblStudent
StudentID -primary key
Lastname
Firstname
etc.

tblInstructor
TeacherID
Lastname
etc.

tblTopic
TopicID -primary key
CourseDescription

tblCourse
CourseID
TopicID - foreign ley (related to tblTopic)
Semester/Year
ClassTime

tblLocation
LocationID- primary key
Bldg (could also have a related table of bldgs)
Room# (could also have a related table of rooms)

tblAttendance (courses)
ClassID -primary key
StudentID -foreign ley (related to tblStudent)
CourseID - foreign ley (related to tblCourse)
LocationID - foreign ley (related to tblLocation)
TeacherID - foreign ley (related to tblInstructor)
ClassDate
Grade
etc.

If you make your data entry forms "Add Only", then no one will be able to
edit existing records.
Settting up DAP's is quite a challenge for an Access beginner, more so if
payment info is needed. You might consider scaling this back. Hiring a
database consultant to create this for you would, I estimate, cost
$2000-4000 and take 3-6 months. Have you searched for existing commercial
products?
-Ed