student database

[ccg]

Hi, I have been trying to teach myself Access and design a database to record
students' mandatory community service hours for about 8 months now.Just when
I think I understand, I come up against a wall.

This is what I have so far and am open to any and all suggestions.

tblStudents; StudentID, StudentLastName, StudentFirstName, GradeID,
StudentNumber, GradYr, EnrollDate, Status (enrolled, graduated, withdrawn),
etc (Various bio info, address and so on).

tblGrade; GradeID, GradeName (fresh. soph etc), GradeReqd (hours reqd for
each grade 10 hours for fresh, 20 hours for soph).

tblServiceDetail; ServiceDetailID, StudentID, SchoolYrID (which school yr
to apply hours to, 2005-06), DateOfService, HoursServed, OrganizationID
(Organiz. where hrs were served), ServiceCategoryID (feed the poor, tutor
children, etc)

tblServiceRecord (link table between Student and Service Detail);
ServiceRecordID, StudentID, ServiceDetailID

tblOrganization; OrganizationID, OrganizationName, ServiceCategoryID, etc.
bio info

tblServiceCategory; ServiceCategoryID, ServiceCategory

tblStatus; StatusID, Status

tblSchoolYear; SchoolYrID, SchoolYear

I created a Student Service Record form that has basic student info with a
Student Name combo box to go to each student service record. It has a command
button to go to a form to input a new service record. I also want to have
either a subform or tabbed forms to show the service record for each year of
their high school career. I may have to input service hours for a student's
freshman year when they are a junior. That is why I have the School year
reference.

I want to be able to pull up a student and view the current years service
hour status and if necessary easily look at any of the past years record to
see if they passed for that year. I need to be able to see the details of
each year so I can tell the student what hours have been recorded for them.

Besides the best way to set up the forms and relationships, I am stumped as
to how to show each year of service. Each year I will import from Excel the
current info for the Students table and I am not sure how that will work. The
school database has the Status field (enrolled, withdrawn, graduated) so I
included that in the Students' table. When I scroll through the Student Combo
box, I only want to see currently enrolled students and I am not sure of the
best way to do that.

I also want to be able to easily see who has not passed for the current
year. I know I can do that through a report but is that the most efficient
way and what is the best way to set it up.

Well how is that for a question!! Any help is appreciated. For 8 months, I
have bought books and scoured the internet, and have taken 2 online courses
trying in vain to complete this project. I need to move on. Thanks.

 

[tina]

comments inline.

Hi, I have been trying to teach myself Access and design a database to record
students' mandatory community service hours for about 8 months now.Just when
I think I understand, I come up against a wall.

This is what I have so far and am open to any and all suggestions.

tblStudents; StudentID, StudentLastName, StudentFirstName, GradeID,
StudentNumber, GradYr, EnrollDate, Status (enrolled, graduated, withdrawn),
etc (Various bio info, address and so on).

tblGrade; GradeID, GradeName (fresh. soph etc), GradeReqd (hours reqd for
each grade 10 hours for fresh, 20 hours for soph).

tblServiceDetail; ServiceDetailID, StudentID, SchoolYrID (which school yr
to apply hours to, 2005-06), DateOfService, HoursServed, OrganizationID
(Organiz. where hrs were served), ServiceCategoryID (feed the poor, tutor
children, etc)

tblServiceRecord (link table between Student and Service Detail);
ServiceRecordID, StudentID, ServiceDetailID

you don't need tblServiceRecord at all. a linking table is only required to
join two tables that have a many-to-many relationship with each other - and
so cannot be joined directly. tblStudents is already *directly* related to
tblServiceDetail by the StudentID field in both tables. that relationship is
set up correctly, because there is a one-to-many relationship between
tblStudents and tblServiceDetail: one student may have many service detail
records, but each service detail record is related to only one student.

tblOrganization; OrganizationID, OrganizationName, ServiceCategoryID, etc.
bio info

tblServiceCategory; ServiceCategoryID, ServiceCategory

tblStatus; StatusID, Status

tblSchoolYear; SchoolYrID, SchoolYear

I created a Student Service Record form that has basic student info with a
Student Name combo box to go to each student service record. It has a command
button to go to a form to input a new service record. I also want to have
either a subform or tabbed forms to show the service record for each year of
their high school career. I may have to input service hours for a student's
freshman year when they are a junior. That is why I have the School year
reference.

I want to be able to pull up a student and view the current years service
hour status and if necessary easily look at any of the past years record to
see if they passed for that year. I need to be able to see the details of
each year so I can tell the student what hours have been recorded for them.

Besides the best way to set up the forms and relationships,

whoa! stop and re-arrange your thinking here. your *tables* should be built
and their relationships *with each other* set up, before you ever go near a
form.

I am stumped as
to how to show each year of service. Each year I will import from Excel the
current info for the Students table and I am not sure how that will work. The
school database has the Status field (enrolled, withdrawn, graduated) so I
included that in the Students' table. When I scroll through the Student Combo
box, I only want to see currently enrolled students and I am not sure of the
best way to do that.

I also want to be able to easily see who has not passed for the current
year. I know I can do that through a report but is that the most efficient
way and what is the best way to set it up.

Well how is that for a question!! Any help is appreciated. For 8 months, I
have bought books and scoured the internet, and have taken 2 online courses
trying in vain to complete this project. I need to move on. Thanks.

well, you're essentially asking how to set up the entire user interface.
everything you've described is do-able in Access, with varying degrees of
complexity in the GUI design, and various choices of approach - but to tell
you step-by-step how to do it all is really, IMO, outside the scope of this
forum.

i can tell you that, in general and with the exception noted above, your
tables structure looks reasonable (i can't state that with more confidence
because you summarized some of the tables' structure, rather than listing
every field for review). you may not believe it, but that's half (or two
thirds) of the battle right there. good luck with the rest of it.

hth

 

[ThePandaManCan]

If I may,

Check out the book Microsoft Office: Access 2003 Inside Out (very
technical) OR Microsoft Office Access 2003 Step by Step (less
technical). When I was teaching myself access, these books helped out.

-ThePandaMan

 

[dropdown3 via AccessMonster.com]

Microsoft has a classroom management database located at:
http://office.microsoft.com/en-us/templates/TC010184071033.aspx?CategoryID=CT011366821033


-Charles-

 

[ccg]

Thanks for the suggestion about the Service Record Detail table, I was
wavering on that. That is the kind of comment I was looking for,

I didn't want anyone to setup "the entire user interface." I just wanted
suggestions if it looked as if I was heading down the wrong track. I spent a
LOT of time trying to develop the tables and their relationships and looked
at countless db sturctures. There comes a time though, when you just have to
jump and let 'er rip so I moved toward form development. I have seen a lot
of posts that give a great amount of detail and suggestions and was looking
forward to any constructive advice that could be thrown my way.

 

[ccg]

Thanks I have used that to model some of my structure. I think where I get
stuck is maintaining the History of the hours through each grade. And how to
efficiently post the hours to each grade and then show an itemization by
grade. I have not been able to find a db that is similar enough to be able to
model it or learn from its structure.

 

[Brendan Reynolds]

You might find something useful at the following URL. There are a couple of
student-related data models there ...

http://www.databaseanswers.org/data_models/index.htm