I'd like to thank any and all who take the time to read about my issue and offer to help. I posted another thread on this board about a specific issue, but now I'm backtracking and rethinking my whole design. I am setting up a database to analyze tests. I need to be able to analyze a test question by question. For example, how many students got question #6 correct on test MA7. I also need to be able to calculate a particular student's test score and summarize scores based on team and grade level. I have come up with a design that works; basically, every question on every test for every student is a separate record. I am able to get the information I need, however, with 1700 students, 25 tests each, with 10-15 questions per test, I will end up with approximately 500,000 records. Sorting and finding with that many records is prohibitively slow.

I have also set up a separate table in which each record represents 1 test for 1 student and the answers are contained within a repeating field. This solution doesn't allow me to analyze one particular important aspect of these tests, benchmarks. Each question on a test has a label called a benchmark (the concept a student was supposed to use to answer the question). For example, on test MA7, question numbers 1 - 4 might be benchmark MA.7.G.3.3, while questions 5 - 10 might be benchmark MA.7.G.3.1. I have to be able to find a student's performance, team’s performance, and grade level’s performance on a particular benchmark.

Any advice/guidance that you can offer is appreciated. Thanks.

I need to be able to analyze a test question by question.

I don't think there's a way to do this other than having a separate record for every answer. If it means 500k records, then that's what it will take. Have you tried this in practice? Keep in mind that finds and sorts will speed up once the data gets indexed.