Sorting in a Report

[RayMilhon]

I have a Database for a bowling league. I have a report that shows the standings based on a query that is sorted by Percentage calculated by adding (wins + ties)/(wins + losses + ties) To give a percentage of wins. It works except 2nd and 3rd place are switched 2nd place has a win percentage of .56 and 3rd has .53 but the query and report shows the team with .53 in 2nd place and .56 in 3rd. I don't understand why it does that. Nothing I do changes it. Any thoughts?

[Welshgasman]

Reports take NO NOTICE of the data order in it's source.
You use the Sort option in the report.

[RayMilhon]

Reports take NO NOTICE of the data oder in it's source.
You use the Sort option in the report.

I sort it in the query. I also use the sort in the report it doesn't change anything

[orange]

Ray,
Sorting in the query is ignored when reporting via Report. When using Report, as welshgasman said, you must use the sorting option within the Report---see Group, Sort, Total in Report options.

If this doesn't help, then you might want to post a copy of the database for others to "attempt solution".

[davegri]

You need to sort descending, that will make highest values first.

[RayMilhon]

As I stated earlier I sort in the query and in the report. doesn't change. davegri I do sort descending every other record 1st and 4th through 7th are sorted correctly it's 2nd and 3rd that are switched

[RayMilhon]

tried to attach the file but it's too big by .10 MB tried compact and repair and zipping it but 2.10 is the smallest I can get it and the error message says 2.0 is as big as I can upload.

[davegri]

Can you post an image of the report (or a pdf of the report)?
It could be that you are sorting on fields in addition to the average, and those sorts have higher priority than the averages.

[Micron]

If the copy your file is too big after being zipped and it has embedded images or attachment fields, removing those will be a big help. You could also delete tables/queries/forms/reports not needed to replicate the issue. Just make sure that what you end up with produces the same problem.

EDIT - I was also wondering if your field data type is String, but I can't see that being the issue for the values you posted (.56 and .53). Doesn't seem as though it could be due to rounding either.

[Welshgasman]

tried to attach the file but it's too big by .10 MB tried compact and repair and zipping it but 2.10 is the smallest I can get it and the error message says 2.0 is as big as I can upload.

Create a new DB and import ONLY what is needed to see the issue.

[CJ_London]

Are you sorting on any other field?

[Minty]

Just to reiterate what others have said, the report creates it's own version of the query data and applies sorting and grouping to it internally based on the settings in the report.
There is zero point in applying any sort order to the reports recordsource, as it is never used, and will simply slow down the operation of the query on larger datasets.

I suspect you have something (a grouping or section sort order) in the report that is overriding your desired results.

[RayMilhon]

Can you post an image of the report (or a pdf of the report)?
It could be that you are sorting on fields in addition to the average, and those sorts have higher priority than the averages.

Here's a screenshot of the report design and the report preview.
Here is the query. All fields in tbl)standings are numeric the only field that is not numeric in this report is the Teams.team field which is the team name.

SELECT Teams.Team, tbl_standings.Wins, tbl_standings.losses, tbl_standings.Ties, ([wins]+([ties]*0.5))/([wins]+[Losses]+[Ties]) AS PCT, tbl_standings.[Total Pins]
FROM tbl_standings INNER JOIN Teams ON tbl_standings.TeamID = Teams.ID
ORDER BY ([wins]+([ties]*0.5))/([wins]+[Losses]+[Ties]) DESC;

[Gicu]

Can you try to wrap the PCT calculated field in a CDbl() or CSng() function and see what you get?

Cheers,

[Micron]

Maybe you can't reliably sort on a calculation. However, you already have assigned that expression/calculation to PCT so why are you not just sorting on PCT?