Report Sorting using percentage (1 Viewer)

jeffreyccs

New member
Local time
Today, 06:02
Joined
Nov 10, 2012
Messages
29
Hi

I have a problem creating a sort on a report containing students test results. I would like to sort on the students overall percentage correct but cannot find a way to do it. The student sub totals are a calculated field and Access does not seem to allow sorting on an expression. I looked at creating an expression in the source query but can only manage it for the individual test results.

Any advice would be very welcome

Regards

Jeff
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:02
Joined
Jan 23, 2006
Messages
15,364
"sort on a report .."
Reports have their own Sorting and Grouping. You'll have to use the Report sorting functionality.
 

plog

Banishment Pending
Local time
Today, 08:02
Joined
May 11, 2011
Messages
11,613
I would like to sort on the students overall percentage correct but cannot find a way to do it.

Please post sample data to demonstrate how you calculate "overall percentage".
 

jeffreyccs

New member
Local time
Today, 06:02
Joined
Nov 10, 2012
Messages
29
Hi

Thanks for the reply.

I am calculating percentages in both group and overall totals using
=Format(Sum([Correct])/Sum([Questions]),"0.0%")

Regards

Jeff
 

plog

Banishment Pending
Local time
Today, 08:02
Joined
May 11, 2011
Messages
11,613
I need to see the data in the table(s). Please provide 2 sets of data:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results. Show me what you expect the data in A should like like when all is said and done.
 

zeroaccess

Active member
Local time
Today, 08:02
Joined
Jan 30, 2020
Messages
671
Reports ignore sorting settings in the source table/query.

On the Data tab of your Report Properties, under the record source, look at the Order By field. Do something like this, using your field name:

[PercentageCorrect] DESC
 

jeffreyccs

New member
Local time
Today, 06:02
Joined
Nov 10, 2012
Messages
29
Hi

I apologize for the delay in replying.

I have put together a small example showing the report and it is the sub totals in red that I am trying to sort on using percentage as the basis.

Thanks for your comments

Regards

Jeff
 

Attachments

  • Sample.zip
    40.6 KB · Views: 114

Bullschmidt

Freelance DB Developer
Local time
Today, 08:02
Joined
May 9, 2019
Messages
40
o Changed the name of the query from qryTestResults to be qryTestResults2.

o Created a new totals query called qryTestResults1 with the following:

- TraineeID field as follows:
- Total set to be Group By

- PercentageCorrectCalcAndTraineeID field as follows:
- Total set to be Expression
- The top being PercentageCorrectCalcAndTraineeID: Str(Round(Sum([Correct])/Sum([Questions]),4)) & " - " & [TraineeID]
- So sample data for the PercentageCorrectCalcAndTraineeID column looks like this (i.e. shows as text with fraction correct, space, dash, space, TrainerID):
.9083 - 1
.7612 - 2
.9362 - 3

- And here is sample data for the whole query:
TraineeID PercentageCorrectCalcAndTraineeID
1 .9083 - 1
2 .7612 - 2
3 .9362 - 3

o Then modified qryTestResult2 which includes the tblTestResults table to also include the new qryTestResults1 totals query with a join on the TraineeID field. Then added the new PercentageCorrectCalcAndTraineeID field to the query (and this will be used as the first Group field in the report).

o Then modified the report to change its first Group from TraineeID to instead be the new PercentageCorrectCalcAndTraineeID field. And then changed the sorting for this group from A-Z to now be Z-A.

o And then things work as desired.

By the way the reason the PercentageCorrectCalcAndTraineeID field includes the "AndTraineeID" part is just in case there are any ties such as multiple students with the same overall percentage so that they wouldn't all be lumped together. One minor issue here is that in case there actually are any ties then the TrainerID's are sorted in descending order for those ties when ascending order might actually be more asthetically pleasing.
 

Attachments

  • Sample.zip
    38.9 KB · Views: 107

Bullschmidt

Freelance DB Developer
Local time
Today, 08:02
Joined
May 9, 2019
Messages
40
After thinking some more about the sorting in case of ties issue here is a workaround:
In qryTestResults1 change this:
PercentageCorrectCalcAndTraineeID: Str(Round(Sum([Correct])/Sum([Questions]),4)) & " - " & [TraineeID]

To be more like this instead (putting "1-" i.e. one minus in there):
PercentageCorrectCalcAndTraineeID: Str(1-Round(Sum([Correct])/Sum([Questions]),4)) & " - " & [TraineeID]

And perhaps change the name to be more descriptive of the latest so change PercentageCorrectCalcAndTraineeID to now be called OneMinusPercentageCorrectCalcAndTraineeID.

And then in the Group on the report can sort A-Z (ascending) instead of Z-A (descending).

And another thing to consider is that the sorting is on a TEXT field instead of a number field, and in this case that hopefully still works. But in some other cases if not careful how it's set up you could sort ascending and have something like "350" showing up before "75" based on the first character.
 

statsman

Active member
Local time
Today, 09:02
Joined
Aug 22, 2004
Messages
2,088
Just for future ref. Access will not allow sorts on expressions in queries.
However, it will allow them using the sorting and grouping function in the report.
I just discovered this myself after trying to get a query sort on a percentage expression.
One of those "more than one way to skin a cat" things.
 

Bullschmidt

Freelance DB Developer
Local time
Today, 08:02
Joined
May 9, 2019
Messages
40
Just for future ref. Access will not allow sorts on expressions in queries.
However, it will allow them using the sorting and grouping function in the report.
I just discovered this myself after trying to get a query sort on a percentage expression.
One of those "more than one way to skin a cat" things.

Not sure what expressions you were dealing with (maybe something really complicated, or maybe just the fact that in a totals query things are always sorted by the grouped columns first) but just tested and this PercentageCorrectCalcAndTraineeID calculated field was able to be sorted in the queries.
 

Users who are viewing this thread

Top Bottom