X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Sort A Report By A Calculated Field Inside The Report, Access 2007    
post Nov 16 2017, 04:14 PM

Posts: 11
Joined: 16-November 17

Database has a Student table which is linked to a Math Table, English Table, Science Table, and SpcWorks Table. Built forms for all as well. I input all student data, i.e., Lname, Fname, address, phone, etc. Then open the Math table with all the Math scores. Math table… Test1, Quiz1, Test2, TotalMathScore. Each Math table is linked to one student by StudentID. Same for English, Science and SpcWorks.

Report works well - Lists each Student - [Lname] [Fname] [TotalMathScore] [TotalEnglishScore] [TotalSciScore]
Very Happy… but… was asked to place students in groups by teams. I added a Team Name field to the Student Table. Then did a Query: [TeamName] [Lname] [Fname] [TotalMathScore] [TotalEnglishScore] [TotalSciScore] [TotStuScore]
Where TotStuScore= [TotalMathScore]+ [TotalEnglishScore]+ [TotalSciScore] Worked like a charm. So I send it to report by Team Scores. Now I have a grouped report by Team Names with the Students listed below. EX
Team: CATS
John Student1 221
Mary Student2 150

Jane Student3 250
Thomas Student4 150

William Student3 250
Tad Student4 100

The Report Allowed me to Sum each of the Student’s scores to come up with a Team Total =Sum([TotStuScore])
Ex. CATS = 371
DOGS= 400
Birds = 350
These Summed numbers are place in a field in the Team Groups footer. So here is my problem. I want to sort this report descending… with the Team with the highest score on top. But Access will not allow that. Is there a way???
Go to the top of the page
post Nov 16 2017, 04:42 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


> Math table… Test1, Quiz1, Test2, TotalMathScore

Based on the above, your data isn't properly Normalized, so it may not be do-able, at least not easily. You're trying to use an Excel strategy for database tables. In properly Normalized data, all test scores should be in one field, you should not have separate tables for subjects, and you rarely want to store calculated values like the "total math score". Here's a basic table structure for this sort of data:

SubjectID [Autonumber, Primary Key]
strSubjectName (separate records for "Math", "English", "Science", etc.

TeamID [Autonumber, Primary Key]
strTeamName (one record for each team)

StudentID [Autonumber, Primary Key]
lngTeamID [Foreign Key to tblTeams.TeamID]

TestID [Autonumber, Primary Key]
lngSubjectID [Foreign Key to tblSubjects.SubjectID]
strTestDescription ("Science quiz #3", "English Final", etc.)

TestScoreID [Autonumber, Primary Key]
lngStudentID [Foreign Key to tblStudents.StudentID]
lngTestID [Foreign Key to tblTests.TestID]
intScore (all test scores go here. The english quiz, the science final exam, everything.)

The tblTestScores table that stores the actual scores is known as a Junction Table, and it creates the many-to-many relationship between students and tests (one student takes many tests and one test is taken by many students). If one student can belong to more than one team, you'd create a tblTeamMembers Junction Table to handle the many-to-many relationship between students and teams, and move the lngStudentID field from the students table to the new junction table.

This setup allows for any number of test subjects, any number of tests and even allows a student to re-take a test, if you like. For a new subject, just add a record to the tblSubjects table. For a new test, just add a record to the tblTests table.

You use the Sum function to find values like the total math score, rather than storing them. You use the Max function to find things like the highest team score.

Creating a properly Normalized table structure is the single most important step in creating a database. It's like the foundation to a house; if the foundation isn't solid, the whole house is going have constant problems. For more information on getting started with Normalizing your data and other aspects of database creation, check out our Newcomers Reading List.

Hope this helps,

Go to the top of the page
Doug Steele
post Nov 16 2017, 04:43 PM

UtterAccess VIP
Posts: 21,969
Joined: 8-January 07
From: St. Catharines, ON (Canada)

As far as I know, you'd have to have a query that calculates the team scores and join that query to the query with the individual student scores so that all the information is available in the report's RecordSource. You'd then be able to sort on the team score.
Go to the top of the page
post Nov 16 2017, 05:37 PM

Posts: 11
Joined: 16-November 17

Ugh!!! Ok Doc... I know you're right. LOL I was kinda hoping for a simple fix but I know I probably need a better developed structure like the one you provided. Thanks!!!
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 07:05 AM