UtterAccess.com
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    
 
   
ewayne99
post Nov 16 2017, 04:14 PM
Post#1



Posts: 2
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

Team DOGS
Jane Student3 250
Thomas Student4 150

Team: BIRDS
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
 
doctor9
post Nov 16 2017, 04:42 PM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


ewayne99,

> 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:

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

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

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

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

tblTestScores
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,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Nov 16 2017, 04:43 PM
Post#3


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


welcome2UA.gif
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
 
ewayne99
post Nov 16 2017, 05:37 PM
Post#4



Posts: 2
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    16th December 2017 - 07:28 PM