Help with Database design!!!!!

[Virnocis]

I'm fairly new to Access, and I'm trying to create a database with
multiple relationships. This is what I want to do:

Search a student to find:
The Class they are in
The location of the class
The Instructor who taught
The week the class was taught
The name of the workshop

I want to be able to search all of these fields for the match. Another
example would be:

Search an instructor to find:
The Classes they taught
The location and date of those classes as well as the workshop name
The students who attended those classes.


I've been trying to Use the Class as the junction table with the
following IDs:

ClassID
DateID
LocationID
InstructorID
WorkshopID


The problem I'm having is when it comes to class IDs and relating them
to the other tables. Since both instructors, students, and dates can
have many class IDs.

What I tried to do was assign multiple class IDs to the students,
instructors and dates, and it looks like this:

for dates:

dateID weekdate ClassID1 ClassID2 ClassID3 ClassID4

for students:
StudentID ......... ClassID1 ClassID2

and for instructors:
InstructorID ....... ClassID1...ClassID3...
lassID6...ClassID8...ClassID10

I'm having problems with ClassID matching with all the other columns,
and I don't know what to do. Can anyone help? thanks.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TrainingRegistration.mdb" which is a fully functioning
registration application for training classes. Looking at that might give
you some ideas.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

Students and Classes happens to be an example often used in teaching database
design. Here is the basic theory.
Students attend one or more occurances of classes
Classes occur on one or more occasion

That indicates a many to many relationship between the two entities.
Relational databases cannot have direct many to many relationships. To
resolve a many to many relationship, it requires a third entity or as
commonly named, a junction table. In this case, notice the word "occur" or a
variation of is in both statments. So we need a table that identifies these
occurances. In most cases, you will find that what you are referring to as a
Class is actually Course and a Course is taught in a Class.

An instructor may teach one or more Classes.
A Class is taught by one instructor. So the Instructor is one to many to
Classes, bu has no relationship to courses. (We will ignore for simplicity
that perhaps you want to maintain information on which instructors teach
which classses, but once you get this part, it will become self evident)

So we need 5 tables, Student, Instructor, Course, Class, and StudentClass.

tblStudent
StudentID - AutoNumber Pk
(student demographic info)

tblInstructor
InstructorID - AutoNumber Pk
(instructor demographic info)

tblCourse - AutoNumber Pk
CourseID
(information required to identify the course, the adendance requirements,
requirements

tblClass
ClassID - AutoNumber Pk
InstructorID - FK to tblInstructer
CourseID - FK to tblCourse
Locations
Date
(other Class related info)

tblStudentClass
StudentClassID - AutoNumber Pk
ClassID - FK to tblClass
StudentID - FK to tblStudent

Notice what this last table does. It shows every student enrolled in every
class. When you are doing searchs or reporting on your database, this table
can be a starting table to use in queries where you are trying to match who
attended what.

 

[Virnocis]

Aha!!! I see how to get around the multiple columns of class IDs!

I just need to start building queries of each, as well as create the
form to make it user friendly!

Any pointers for either?

My biggest trouble is when i have to get into code, and that's where i
can't tell my earhole from my a-hole.

Thanks for the help so far!

 

[Klatuu]

Can't give you much with the queries without knowing what you want to
retrieve, but as to an Enrollment form, I would suggest using a Class as the
record source for a main form and a query based on the student table and the
StudentClass table as the subform's record source.

 

[Virnocis]

Well, this is what I wanted it to do:

a search for what box: e.g. student
How to search it box: e.g. last name

and I want to be able to search like this:

Student: show classes the student attended (with each class, list the
Date,Workshop,Location, and Instructor)

Date: show classes on that date (with each class, list the Workshop,
Location, Instructor, as well as an option to show the students
attending)

Location: show the classes at that location, and each class lists
(date,workshop,location,instructor, and a selection to view students
at that class

It sounds pretty simple, but i'm having trouble with it.

 

[Tony Toews [MVP]]

I provide help with Access applications for a very reasonable fee. I can
create the database for you for a very reasonable fee. If you are
interested, contact me at (e-mail address removed).

Steve, please stop soliciting work in these newsgroups. These are
meant for free assistance.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[John W. Vinson]

Well, this is what I wanted it to do:

a search for what box: e.g. student
How to search it box: e.g. last name

and I want to be able to search like this:

Student: show classes the student attended (with each class, list the
Date,Workshop,Location, and Instructor)

Date: show classes on that date (with each class, list the Workshop,
Location, Instructor, as well as an option to show the students
attending)

Location: show the classes at that location, and each class lists
(date,workshop,location,instructor, and a selection to view students
at that class

It sounds pretty simple, but i'm having trouble with it.

The Form Dave suggested will do exactly this. Did you try it? What went wrong
if so?

Note that "Mark" is actually Steve Santos, who's notorious for soliciting
business and offering his dubious services for pay in this *unpaid volunteer*
newsgroup. I'd ignore him.

 

[Virnocis]

<sarcasm>
Hey Steve, welcome back !!
</sarcasm>

==> So why don't you STAY away ??
==> STILL every week lots of pagehits and new first-time visitors at http://home.tiscali.nl/arracom/whoissteve.html
Yes, even after 8 months of your absence still weekly hits !!

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

To *anyone* here in this group:
Feel free to show the linkhttp://home.tiscali.nl/arracom/whoissteve.html
anytime this so-called Mark (=Steve Santus) advertises here.

Arno R

ok, I guess I got the picture about steve.

As for the form, I didn't really get that whole course part, and I
didn't see any reason for it, so I left it out.

i created three junction tables, a studentClass, instructorClass, and
dateClass.

I'm still having trouble with making the search forms...

 

[Virnocis]

Thanks, but can you please stop posting useless text in this
discussion? I don't think your services are required here.

I've been working on the forms, but they don't look pretty at all.

recently, I read on another site about creating a search. What I did
was use an existing query that looks for:

Student first name, last name, weekdate, workshop, location, and
instructor.

The query works. I put this part for the criteria: Like [Forms]!
[frmSearch].[name] & "*"

I created a form called "frmStudentClassInfo," and I used the query to
show the info.

I then used frmStudentClassInfo as a subform in the form called
frmSearch.

Right now, frmSearch has these things:

Search what? (followed by an empty combo box)

Enter Text to Search: (unbound text box called "name") [Search]
button that has event procedure enabled with
Private Sub Command4_Click()
Me.Refresh

End Sub

(in VB)

Couple of things. when i try to type in a name, i get a prompt to
enter in data in a text box. I basically have to reenter the name for
it to work.

The subform display looks like crap. Literally like just another form
within a form, and I want it to look nicer, but i don't know how to do
it.

Anyone except Steve Santos can reply =)

I hope i didn't confuse anyone.

 

[Tony Toews [MVP]]

Thanks, but can you please stop posting useless text in this
discussion? I don't think your services are required here.

The subform display looks like crap. Literally like just another form
within a form, and I want it to look nicer, but i don't know how to do
it.

I'm not sure exactly what are trying do with a subform but you
probably want the subform to be a continuous form so it, somewhat,
looks like the datasheet view. (Or somewhat like Excel.) You may
need to move the fields around in a columnar format or recreate the
for as a columnar form. Then change the subform Default View property
to Continuous Forms.

Anyone except Steve Santos can reply =)

<bigger chuckle>

Steve can be quite annoying.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Virnocis]

I'm not sure exactly what are trying do with a subform but you
probably want the subform to be a continuous form so it, somewhat,
looks like the datasheet view. (Or somewhat like Excel.) You may
need to move the fields around in a columnar format or recreate the
for as a columnar form. Then change the subform Default View property
to Continuous Forms.

is there anyway i can upload the file, so people can have an idea of
what i'm trying to do?