i need help with a kind of filtering app

[silverpatronus]

ok, i haven't programmed in ever and now i need to do something for work so HELP!

ok, i work for an educorp, and the problem is this: i have a master timetable, and from that i want to generate personal timetables for each student. for example, let's say that on the master timetable, i have 1st period spanish, 2nd period, english, 3rd period maths and 4th period french, and i have a student taking only english and french. i want to generate for that student a personal timetable that only shows the classes he's taking in their correct time slots. i know that i have to write some kind of comparison loop, but i haven't programmed in ever and i don't know the visual basic code.

can anybody help me out?

 

[lespaul]

What are the fields in your master table? Is there a student table and a registered class table? If your tables are normalized you should be able to get this with a SQL statement.

leslie

 

[silverpatronus]

i have a table with each student's registered classes listed and the master timetable as a table.

 

[lespaul]

What are the fields in the table? Which fields do you want back in your query? How about some sample data from the tables and your expected results? I'd like to help you, but I need some DETAILS.

leslie

 

[silverpatronus]

ok, the student class listing table has the student's name, ID and class 1, class 2 etc, a max of 15 classes. the timetable table is organised by period, so period 1 let's say ist year maths, 2nd year english, 3rd year spanish etc, period 2 1st year spanish, 2nd year french etc. since it's for a lot of different yeargroups, i'll be coding the subjects so that 1st year maths doesn't get mixed up with 2nd year maths etc. what i'm trying to make happen is that when a student wants to see his own personal timetable, the routine will match the subject codes for the subjects they're taking to the subject codes in the master timetable and create a form/report/data access page (not sure which i want to use yet) with just his classes timetabled.

 

[lespaul]

If you are able to change the table design, I would first recommend normalizing your data (you can search the forums for normalized database or see Thread700-628486).

When you have fields named Class1, Class2, etc. that's a sure sign the database isn't normalized. This is a one to many relationship (one student has many classes) and should be in an intermediate table named StudentClasses that has the student's id and the class id.

How is the mastertimetable tied to the student table? Is there a ClassID that is stored in the CLass1 field in the student table?

Without normalizing the data you'll end up with an SQL statement that's probably too long for access to handle because you will have to join the class table 15 times (once for each class) like this:

SELECT * FROM STUDENT INNER JOIN CLASS ON STUDENT.CLASS1 = CLASS.CLASSNAME INNER JOIN CLASS ON STUDENT.CLASS2 = CLASS.CLASSNAME etc up to 15!

So if you can modify your table structure I would do that first. If you can't you'll have to wait for some one else to come up with something better for you.

Sorry,

Leslie

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[silverpatronus]

so you're suggesting a table for each class instead of or in addition to a table with all the students and their listed classes? and that leads me to the question if it's in addition to, how can i auto update the class tables by entering data in the student class listing table?

 

[lespaul]

I would have a class table that lists all the possible classes (only include information about the class):

ClassID(PK)
ClassName

I would have a timeslot table:
SlotID (PK)
SlotName (Period 1, 2, etc)

I would have a classSchedule (composite PK):
ClassID*
SlotID*

I would have a student table:
StudentID (PK)
Name
SSN

I would have a studentClasses table (again a composite PK):
StudentID*
ClassID*
SlotID*

but this is just a basic suggestion, I can't fully define the tables without knowing everything about your system. Once you get your database normalized you can write some update/insert queries to get your existing data into the proper tables and classified.

 

[silverpatronus]

what's a PK? and actually, that looks like it might work! so i match numbers to numbers and output to the personal timetable form/report/data access page?

 

[lespaul]

PK = primary key of the table

 

[silverpatronus]

THANK YOU THANK YOU THANK YOU! this looks like it's going to work.

 

[silverpatronus]

it worked! YAY! but i have another problem of the same kind but slightly more complicated. i have, set up in excel, a gradebook for each teacher to enter grades for the students in his class. i also have set up a master gradebook that i'm using to generate student grade reports. the grade reports work just fine when i enter the grade information into the master gradebook, but i want the grade information to automatically fill in from the individual teacher gradebooks to the master gradebook. if i'm not being clear, i'll try to do screenshots of the gradebooks and upload them.

thanks for all your help. you're really saving my ass here.

 

[lespaul]

If these are excel spreadsheets, I would post in the Microsoft VBA forum Forum707

I believe you will need some sort of code to perform the update when the teacher's workbook is saved/closed. But I'm not familiar enough with it to be able to help someone do it!

 

[silverpatronus]

thanks!