Calculating Percent of a Test Score

[cohnhead]

I have created a database which lists a set number of skills in a table and each student's level of the specific skill ("Mastered" and "Not Mastered").



I have these skill levels defined as the results of a pre-test (ResultPre) field and a post-test (ResultPost) field. Each student has a student id field (StudentID) and a skill field (SkillID) for each skill, along with the pre and post fields.

What I would like to do is find a percentage for each student on the pre test for the total skills "Mastered" and a percent for each student on the post-test for the total number of skills "Mastered".

So, for example:

Student A - Pre-test - 10 skills "Mastered"; 15 skills "Not Mastered" = 40% and on the Post-Test - 20 skills "Mastered"; 5 skills "Not Mastered" = 80%

How can I do this?

[orange]

I think you should start with a review of your tables and relationships. You are more familiar with your environment and the data than readers.
However, I see the following setup for tables as a starting point for consideration. Purple text identifies proposed Table.
Sorry for the bad lines in the drawing --)

Student                                       Skills
  StudentID                                    SkillID
   |    |         StudentPreTestSkills        |  |
   |    |         StudentPreSkillIDPK         |  |
   |    +----> StudentIdFK                    |  |
   |              SkillIDFK<------------------+  |
   |             StudentLevel                     |
   |             PreTestDate                       |
   |                                                 |
   |             StudentPostTestSkills             |
   |             StudentPostSkillIDPK              |
   +---------> StudentIdFK                      |   
                SkillIDFK<---------------------+
                StudentLevel
                PostTestDate

[cohnhead]

Thank you! I have my tables set up pretty well (I think), but I was wondering how to calculate percentages with one of my tables. I have a table which lists every studentID (fk) next to every skillID (fk) and a pretest field (Mastered or Not Mastered) and a post-test field (Mastered or Not Mastered). What I want to know is how I can calculate the percentage of each individual studentID which has "Mastered" on the pre-test, and the same for the post-test.

[orange]

Let's consider you have 30 Skills. Do you expect every Student to Master all of the Skills?
How do you determine which Skills of the 30 total Skills should be used when dealing with Student X?

If all students are expected to Master all Skills then the percentage becomes

Count of Skills a StudentHasMastered/30 * 100

[cohnhead]

Let's consider you have 30 Skills. Do you expect every Student to Master all of the Skills?
How do you determine which Skills of the 30 total Skills should be used when dealing with Student X?

If all students are expected to Master all Skills then the percentage becomes

Count of Skills a StudentHasMastered/30 * 100

I'm trying to set it up like you said Count of Skills a StudentHasMastered/30 * 100, but I want to see the pre-test and post-test side by side.

[June7]

Instead of trying to do all in query which would require multiple and/or nested queries or domain aggregate expressions, consider a report using Grouping & Sorting features and aggregate calcs in header/footer sections. This will allow display of detail records as well as summary data.