Need help setting up database (track test scores)

[VAdataTracker]

I desperately need help in setting up a database to track test scores for
students. I don't want to bore you with details, but should probably give
you some background information.

In Virginia, the state requires that each student pass a certain number of
standardized tests (SOL's) in order to obtain a high school diploma. Before
graduation, each student must pass two English SOL's and must also pass one
math, one science, one social studies, and one "choice" (an additional math,
science, or social studies).

The school has an enrollment of aprox. 700 students. I need to track SOL
scores for each student. SOL's are given in the following categories:
Algebra I, Algebra II, Geometry, Earth Science, Biology, Chemistry, World
History I, World History II, US History, English 11: Reading, and English 11:
Writing.

For each calendar year, tests are given during three separate test
administrations: summer, spring, and fall. All of the above tests are given
three times per year. If a student fails the first time he/she tests, that
student will have opportunity to retest during each subsequent test
administration.

The state mandates that every student enrolled in an SOL class must also
take the corresponding SOL. So, if Jane Doe has already taken her Algebra I
SOL in the Fall 05 test administration, and passed the test (thereby earning
her required math SOL) and then takes Geometry the following year, she must
take the Geometry SOL during the Fall 06 test administration.

My job is to track each student's SOL results, making sure that every
student tests as required, and to be certain that each student is on track
for graduation. I was appointed this position in February. The tracking
method in place when I started this position was via an Excel spreadsheet,
which is very large and hard to use. I think Access is the key to keeping
track of this data, but I don't know anything about Access and I don't know
where to begin.

I know I will need a Student Table with information as it pertains to each
student: ID#, Last, First, MI, Parent Name, Address, etc. What I don't know
is how best to store the test information. Most students test in more than
one subject area during each test administration (with the exception of
summer). For instance, in the S06 test administration, Jane Doe tested in
Algebra I, Earth Science, and World History I.

From what I can understand, I will need a many-to-many relationship in
place, but I don't know whether to try to group all tests together for each
administration in one big table, or if I should make each test session a
seperate table.

As an end result, I would like to be able to run a report that shows every
test a student has taken along with a test score. And just to throw
something else into the mix, I also need to keep track of classroom grade for
each student for each subject. (If Jane Doe passes her Biology SOL, but
failes the class, she will need to take the Biology class a 2nd time, but
won't test in that subject with the rest of her class.) For a student that
is scheduled to graduate in 2008, that student will have potentially taken
SOL's in S08, F07, SU07, S07, F06, SU06, S06, F05, SU05, S05, F04, SU04, and
S04.

I know this is a lot to try to follow. I've been doing this since February
and it still boggles my mind. Any help you can give me would be greatly
appreciated!

Thanks in advance!

 

[tina]

you're correct in thinking that Access is an excellent tool to store and
manage this data. but if you're not familiar with relational database
development, then you're setting yourself quite a task. it IS do-able, if
you're in a position (and are willing) to invest the time it takes to learn
to use the tool properly - which requires that you not only learn how to use
the software (2nd), but also learn the basic principles of relational data
modeling (1st).

for more information, see http://home.att.net/~california.db/tips.html.
also, these newsgroups are a great resource for specific questions, though
they're not the proper forum for broad questions about "how do i build an
entire database".

hth

 

[VAdataTracker]

Thank you for your response and for the link to that website - it's awesome!

I understand that this is a huge undertaking, but unlimately less work than
hand entering data in Excel. I should mention that at the end of every test
session, the state sends reports that are easily imported into Access tables.
These reports have really never been used, because no one was familiar
enough with Access to utilize them. That's a crime, as far as I'm concerned.
I'd much rather invest the initial time it takes to learn something new than
to continue to expend effort doing something the hard but familiar way.

I created a Student Table that contains student specific information. I
created a test table that contains test specific information for the spring
06 session. I established a many to many relationship and created a query to
link the two tables. I was so excited last night when I ran that query and
it worked! I then used the query results to create a report that listed test
results by student. I know this is just a start, but now that I've actually
gotten something to work, I feel much more comfortable about proceeding.

Thanks again for your reply and for the helpful info!

 

[tina]

you're welcome, and good luck with your project.