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 Leagues On Totals Then By Individual Result, Access 2013    
 
   
john_willmott
post Feb 13 2018, 02:27 AM
Post#1



Posts: 519
Joined: 12-July 03
From: South Wales, UK


I am writing a database to collate and report golf league tables.

A fixture is always worth 5 points, halves are possible

The data is held in T_Results
ResultID autonumber
LeagueID FK
HomeTeamID FK
AwayTeamID FK
HomePoints double
Away-points double

When I sort each league, the criteria will be
Firstly by TotalPoints then by

This is easy, but if positions are tied then positions need to be based upon the total of the two fixture results between the two (or more) teams.

At the moment I am thinking of (using via) dumping the leagues into a temporary table with the totals, then looping through a recordset of the tied positions and looking up the result between the two sides to write to an additional sort field - like the old bubble sort. The sortfield will then be the third level orderby for the report.

Anyone have any other ideas, ideally, a way of doing it with just a query or two?

Any suggestions welcome!

John
Go to the top of the page
 
Doug Steele
post Feb 13 2018, 04:03 PM
Post#2


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


I'm feeling too lazy to generate test data today.

Do you have any sample data I can work with?

--------------------
Go to the top of the page
 
john_willmott
post Mar 5 2018, 05:35 PM
Post#3



Posts: 519
Joined: 12-July 03
From: South Wales, UK


Hi Doug, Thanks for the reply.

Sorry I havent replied earlier - I have been away skiing and am now back on task!

Attached is a database with some sample data.
I have not written the report yet, but have done some basic queries.

If you look at Query Q_Total ClubPoints, you will see the league table for the premier league.
The sort is By Total points, then by Away Points. You can see cardiff and aberdare are still tied.

If you look at the form DataEntry, and select Cardiff or Aberdare in the club combobox.
You will see that in the two matches between Aberdare v Cardiff, Aberdare have more points so need to be above Cardiff in the league.

hence my sort is Total Points (Desc), Away Points (Desc), 'Total of 2 Fixtures Between Tied Clubs'

Any Ideas??
John
Go to the top of the page
 
john_willmott
post Mar 5 2018, 05:37 PM
Post#4



Posts: 519
Joined: 12-July 03
From: South Wales, UK


Attachment fell off!!
Attached File(s)
Attached File  Fixtures_Results___Copy.zip ( 81.5K )Number of downloads: 4
 
Go to the top of the page
 
Doug Steele
post Mar 6 2018, 12:35 PM
Post#5


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


Unfortunately, I've developed a wicked cold, so it'll be a while before I can look at this.

--------------------
Go to the top of the page
 
projecttoday
post Mar 6 2018, 07:04 PM
Post#6


UtterAccess VIP
Posts: 9,703
Joined: 10-February 04
From: South Charleston, WV


I took a look and I think you're on the right track. A column based on a function that returns the score with the next team would be very concise but it would be a waste to calculate score differences for all when you only need to break ties. The only thing I could suggest would be to use arrays.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 08:08 PM