Help with a project design?

[machine_wars]

I've got a project to design an app. but I can't think of how to start anyone got some ideas for the following:
PROBLEM:
Student tests have come in from x separate schools. They have been scored on two tests; one for 1st semester work, one for 2nd semester. Score results have been resolved to one final score. A flat ASCII file has successfully been created that has a record for each student with all relevant demographic information and that student’s final score.
Student information includes the following:
Description Type Length
Unique Student ID Num 6
Last Name Char 14
First Name Char 10
Grade Num 2
Score Num 2
Unique School Code Num 4
1st Semester Teacher Char 10 (May be missing)
2nd Semester Teacher Char 10 (May be missing)

The client has requested that a roster of student results be generated for each teacher involved. Rosters should be generated with the following constraints:
· If a student has two different teachers for 1st and 2nd semester, that student will appear on two rosters: one for the 1st Semester Teacher; the second, for the 2nd Semester Teacher.
· If a student has the same teacher for both 1st and 2nd semesters, he will appear on one roster for that teacher.
· If a student has neither a 1st nor 2nd Semester Teacher listed, he will appear (only once) on a special roster listing “No Teacher Name” as the teacher. There will be one of these special rosters for each school if at least one student in that school has neither a 1st nor 2nd Semester Teacher listed.
· If a student has just one teacher name recorded (i.e., the other teacher field is blank), he should appear on only one roster for that teacher name given.

ASSUMPTIONS AND CONSTRAINTS:
· All students must appear on at least one roster.
· No teacher has students in more than one school.
· Teacher names may be missing for either or both semesters.
· There are no two teachers with the same name.
· Only one roster will be generated for any teacher. It will list all students that have been in class with that teacher in either (or both) 1st and 2nd semesters.

YOUR TASK: Design the program solution to create these rosters.
Your design should have a clear statement of the problem and the solution, which can be in any currently available programming language you choose.

If you feel that there is insufficient information, make assumptions. Be sure to document why they are made. State them clearly.

EVALUATION CRITERIA:
1) Clear statement of the problem and a reasoned solution.
2) Successful handling of the following problems:
· Both teachers are blank - Need “No Teacher Name” Roster.
· Only one teacher is given. Student does not end up on “No Teacher Name” Roster.
· Student does not appear 2 times on a teacher roster.
· A “No Teacher Name” roster is NOT generated for schools where every student had at least one teacher name given.
· There is only one roster for any given teacher

[JamesM]

Here goes to start you off:

1. Analyse the data (sql statement) to find out how many students have both teacher's fields blank.

2. Create a report based on the query in 1.

3. Second select sql based query to remove the students in 1 from total students. Note no need to group the data here.

4. Create a Report based on query in 3, group the report by teacher then semester. So report will be

Teacher1
                 Semester1
                                     Student1
                                     Student2
                                     Student3

                 
                 Semester2
                                  
                                     Student1
                                     [Student2] is NOT listed, as is not present with this teacher in semester 2
                                     Student3
                                     Student 4 (had a blank for semester 1)

Teacher2: etc

If I had to create these reports and the only requirement was to produce a hardcopy of the rosters, I would pop it in Access and you are done in 1 hour start to finish.

Regards

James

[machine_wars]

JamesM,
Thanks for the input. I can only use Access to pull info from, I have to write a program in VB to accomplish the calculations and comparisions. Do you think it would be best to create one table or split them up among several?

[JamesM]

I can only use Access to pull info from, I have to write a program in VB to accomplish the calculations and comparisions. Do you think it would be best to create one table or split them up among several?

What calculations? All done with SQL, so you only need to import the data, write a couple of queries and construct the reports. Access is recommended here only as the quickest solution.