i need help doing updates/filtering

[silverpatronus]

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

i work for an educorp, and the problem is this: 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 in access. 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.

 

[SkipVought]

Hi,

You ought to have ONE GRADEBOOK TABLE (Sheet). One of the columns might be Teacher.

Then you have a Form -- could be the built-in form in Excel to load the data. Set the workbook up as shared.

Alternatively, if you must have separate workbooks for each teacher, at least include the Teacher column in each table. Then you can copy 'n' paste OR have a macro that loads the GRADEBOOK TABLE from all the teacher workbooks.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[silverpatronus]

ok, i'm confused. could you kinda walk me through that?

 

[SkipVought]

A Table of this sort ought to have all the informaton necessary, for instance:
Instructor
ClassID
ClassDate (English 101 might be offered more than once)
ClassTime
Student
GradePeriod
GradeEntry
GradeValue

So you set up whatever data structure is required for your circumstance - usually in ROW 1.

Then to load the data using a form, you could use
menu item Data/Form. You could design your own form. You could use a separate sheet as a data form. But ALL the data goes into this table.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[silverpatronus]

hmmm...i dunno. y'see, the problem there is security. we don't want teachers to be able to tamper with other teachers' grades, or with the class lists, or with anything. is there a more secure solution?

 

[Bowers74]

Try having one grade sheet per teacher and hiding all of the other sheets based on the password entered (you will have to create a logon UserForm for this) I did that for the vacation scheduling system for my dept. We didn't want any one changing the other person's vacation to their advantage either.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Silver,

If I were designing this, I'd have a hidden master and a single sheet data entry/inquiry/update form that would permit access to data by instructor/password.

But since you may not want to or may not be able to go to that level design and coding, separate sheets may be the way to go.

Then you could write a simple collect 'n' load routine to populate the master table.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[silverpatronus]

skip, that sounds feasible, except that i can't program worth shite. it's been aeons since i've had to, and everything's left my head.

the reason each teacher has a gradebook or gradebooks and not a single gradesheet is that some teachers teach several subjects, and several yeargroups. so for each teacher, i created a gradebook for each subject they teach, and in each gradebook, there are separate sheets for each yeargroup. i think i'm going to try to post up some screenshots to make this a bit clearer.

 

[SkipVought]

Send a sample of what you have in mind to me at

skip.metzger@lmco.com


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com