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 also want to create forms so that I can add:
new classes
add classID to instructor table
add classID to student table
add classID to date table

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

 

[virnocis]

I actually used a system that eliminates the multiple classIDs by creating a couple of junction tables: DateClass, StudentClass, and InstructorClass.

since those were the only tables with multiple classIDs, I figured that these junction tables will eliminate those pesky classID#s.

I'm now trying to work on making something user friendly, so people can search date,location,student, or instructor to find the information on the rest.

here is the example I used:

search student name: find classes attended, with each result, list the: date,location, workshop, instructor

as well as when using this search to find students:

search instructor name: find classes taught, with each result listing the: date, location, workshop, and presenting a clickable button to view the student roster.

Haven't seen much action on this forum, but i'll keep at it.

 

[DCrake]

Simple Software Solutions

When is your assignment due in

 

[virnocis]

by thursday... =\ I want to finish as soon as possible though.

 

[virnocis]

so far i can only get the queries to generate the results that I'm looking for, but the data is lumped.

When I open a query, it prompts me with a textbox. I use this textbox to enter the name of the person I'm searching. Sucess!! it works, and generates the list I want.

One problem. That's not how I want to do it! I want to be able to create a form that will make it all nice and shiny!

I've tried creating an unbound text box, as well as creating a button that onClick, goes to the event procedure.

I've also created a subform that will show the results, but again, it's not how i want it to look.

I saw a neat little sample called search2000 that has the results not in a subform, but within the form itself, and the values are not changeable, like my values in the subform.

I'm beating my head against the monitor.

any suggestions (besides stop beating my head against the monitor)?

 

[Zaeed]

with your query, copy the SQL and do it manually in the code, and simply pass in the student name to the end of the query.

i.e.
stringtxt = "SELECT * FROM wherever WHERE studentname = "& txtbox

you could then load that into a recordset and populate a bunch of fields on your form that

 

[virnocis]

holy crap, i think that just went right over my head...

 

[Zaeed]

http://www.access-programmers.co.uk/forums/showthread.php?t=150015&highlight=recordsets
this link might prove useful for a bit of background on recordsets

also, i'm not entirely sure how you want to setup your form, but you could simply set the record source of your form to the SQL statement, and control it from a textbox.

 

[neileg]

When is your assignment due in

I think your irony went unnoticed!

 

[DCrake]

Simple Software Solutions

Neil,

I agree with you entirely

 

[virnocis]

Yaarrrhhh!!!!! Bump.

 

[SaviorSix]

you may want to refer to the Access user manual

just sayin....