Special Sort

amirkharkongor · May 13, 2022

Hi Friends,

I have uploaded a sample database with query named as "HSort"

the query output is

BIbNo	AthleteName	SchoolAssociation
046	ANIMA ARENGH	East Garo Hills District Sports Association
248	Wanda Lyngkhoi	Nongstoin District Sports Association
246	Tiewlari Rynshiang	Nongstoin District Sports Association
190	SILTE G. MOMIN	North Garo Hills District Association
073	SHIDALIN RONGPI	Ri Bhoi District Sports Association
025	Feliscita Rynjah	Shillong Sports Association
024	Arkador Lyngdoh Nongbri	Shillong Sports Association
162	MIRIME D. SANGMA	Tura District Athletic Association
161	RINGRINGCHI K. SANGMA	Tura District Athletic Association
320	BEAUTIFY SHYLLA	West Jaintia Hills District Sports Association
220	IAISHAH DKHAR	West Jaintia Hills District Sports Association

But I want to sort it in the following format

BIbNo	AthleteName	SchoolAssociation
046	ANIMA ARENGH	East Garo Hills District Sports Association
248	Wanda Lyngkhoi	Nongstoin District Sports Association
190	SILTE G. MOMIN	North Garo Hills District Association
073	SHIDALIN RONGPI	Ri Bhoi District Sports Association
025	Feliscita Rynjah	Shillong Sports Association
162	MIRIME D. SANGMA	Tura District Athletic Association
320	BEAUTIFY SHYLLA	West Jaintia Hills District Sports Association
246	Tiewlari Rynshiang	Nongstoin District Sports Association
024	Arkador Lyngdoh Nongbri	Shillong Sports Association
161	RINGRINGCHI K. SANGMA	Tura District Athletic Association
220	IAISHAH DKHAR	West Jaintia Hills District Sports Association

theDBguy · May 13, 2022

Consider adding a sort order column in your table.

The_Doc_Man · May 13, 2022

None of those particular columns or their sort criteria are obvious at first glance. Therefore, a "standard" sort isn't going to work. But there is a way.

Your solution is probably going to involve a fourth "hidden" column in which you place a number that would be used for the sort-order via a query or other method of record display. It would be perfectly permissible to create a query that would present those three fields but that would sort on a fourth field that isn't present in the SELECT clause.

Code:

SELECT BlbNo, AthleteName, SchoolAssociation FROM sometablename ORDER BY HiddenSortField ;

amirkharkongor · May 13, 2022

The_Doc_Man said:
None of those particular columns or their sort criteria are obvious at first glance. Therefore, a "standard" sort isn't going to work. But there is a way.

Your solution is probably going to involve a fourth "hidden" column in which you place a number that would be used for the sort-order via a query or other method of record display. It would be perfectly permissible to create a query that would present those three fields but that would sort on a fourth field that isn't present in the SELECT clause.

Code:

SELECT BlbNo, AthleteName, SchoolAssociation FROM sometablename ORDER BY HiddenSortField ;

I tried this

ORDER BY DCount("1","AthleteEvent","AthleteEvent.SchoolAssociation='" & AthleteEvent.[SchoolAssociation] & "' AND EventID<=" & [EventID]) & AthleteEvent.SchoolAssociation

Sometimes it work and sometimes it dont. I dont know why.

CJ_London · May 13, 2022

how does this differ from your other thread?

Sorting Problem

Hi friends, I have upload a sample database with query named as "Winner" While sorting the timing of the race, the timing of the athlete who did not finished the race (Absent, Disqualified and Did Not Finished) appear in the top of the table. I want to sort the timing in ascending order but...

www.access-programmers.co.uk

amirkharkongor · May 13, 2022

CJ_London said:
how does this differ from your other thread?

Sorting Problem

Hi friends, I have upload a sample database with query named as "Winner" While sorting the timing of the race, the timing of the athlete who did not finished the race (Absent, Disqualified and Did Not Finished) appear in the top of the table. I want to sort the timing in ascending order but...

www.access-programmers.co.uk

Suppose we have the following entries for 100meters Track Event.

BIbNo	AthleteName	SchoolAssociation
046	ANIMA ARENGH	East Garo Hills District Sports Association
248	Wanda Lyngkhoi	Nongstoin District Sports Association
246	Tiewlari Rynshiang	Nongstoin District Sports Association
190	SILTE G. MOMIN	North Garo Hills District Association
073	SHIDALIN RONGPI	Ri Bhoi District Sports Association
025	Feliscita Rynjah	Shillong Sports Association
024	Arkador Lyngdoh Nongbri	Shillong Sports Association
162	MIRIME D. SANGMA	Tura District Athletic Association
161	RINGRINGCHI K. SANGMA	Tura District Athletic Association
320	BEAUTIFY SHYLLA	West Jaintia Hills District Sports Association
220	IAISHAH DKHAR	West Jaintia Hills District Sports Association

We have only 8 Tracks/Lanes and if there are 11 Athletes like above we have to conduct the race in two heats i.e. Heat -1 = 6 Athlete and Heat - 2 = 5 Athlete.

But the Athlete from each School must not compete with each other in the same Heat. i.e. Nongstoin, Shillong, Tura & West Jaintia each must be in different Heats so that they do not compete with each other in the same heat.

I want to auto assign the Heats in one click and the result will as follows :

BIbNo	AthleteName	SchoolAssociation	Heats
046	ANIMA ARENGH	East Garo Hills District Sports Association	1
246	Tiewlari Rynshiang	Nongstoin District Sports Association	1
190	SILTE G. MOMIN	North Garo Hills District Association	1
073	SHIDALIN RONGPI	Ri Bhoi District Sports Association	1
024	Arkador Lyngdoh Nongbri	Shillong Sports Association	1
162	MIRIME D. SANGMA	Tura District Athletic Association	1
320	BEAUTIFY SHYLLA	West Jaintia Hills District Sports Association	2
248	Wanda Lyngkhoi	Nongstoin District Sports Association	2
025	Feliscita Rynjah	Shillong Sports Association	2
161	RINGRINGCHI K. SANGMA	Tura District Athletic Association	2
220	IAISHAH DKHAR	West Jaintia Hills District Sports Association	2

Minty · May 13, 2022

Finally, we are getting a proper description with some rules to determine the sort order.

I'm not sure you can do this in one query, I think you will need to load the runners into a recordset, then loop through them and add them to another recordset, adding the heat numbers based on the number of competitors from the same school. And limit the entrants to 8.

CJ_London · May 13, 2022

what happens if a school submits 3 (or more) runners? would you exclude one of them or run 3 (or more) heats reducing the numbers that run in each heat. Or are they only allowed to submit only 1 or two runners?

amirkharkongor · May 13, 2022

CJ_London said:
what happens if a school submits 3 (or more) runners? would you exclude one of them or run 3 (or more) heats reducing the numbers that run in each heat. Or are they only allowed to submit only 1 or two runners?

Max two runners per school

CJ_London · May 13, 2022

And you don't mind which heat has which runners e.g. there is no preference for

Tiewlari

to be in heat 1

and since you are limited to 8 tracks and 2 runners per school, there can never be more that 16 runners to allocate

amirkharkongor · May 13, 2022

CJ_London said:
And you don't mind which heat has which runners e.g. there is no preference for

Tiewlari

to be in heat 1

and since you are limited to 8 tracks and 2 runners per school, there can never be more that 16 runners to allocate

There can be upto 40 to 50 schools at a time and no preference which runner runs in which heat
So sometimes we have to conduct up to 8 to 9 heats before proceeding to finals

CJ_London · May 13, 2022

well - we are getting there. Next question

lets say you have 45 runners, assume they are all from different schools. How would you want that set up - 5 events of 8 and 1 of 5? or 6 events of 7 and 1 of 3? or perhaps 3 events of 8 and 3 of 7?

edit: fixed typo (3 should be 5)

amirkharkongor · May 13, 2022

CJ_London said:
well - we are getting there. Next question

lets say you have 45 runners, assume they are all from different schools. How would you want that set up - 5 events of 8 and 1 of 5? or 6 events of 7 and 1 of 3? or perhaps 3 events of 8 and 3 of 7?

edit: fixed typo (3 should be 5)

Total No. of Heat = Total Athlete / Number of Lane (45 / 8 = 5.65 round off to 6)
Total No. of Players per Heat = Total Athlete / Total No. of Heat (45/6=7.5 round off to 8)
So the set up will be 5 Heats of 8 Athlete per heat and one Heat of 5 Athletes. Total 6 Heats.

It may also be mentioned here that the no. of Athletes in each Heat should not be less than 3 Athletes

CJ_London · May 13, 2022

It may also be mentioned here that the no. of Athletes in each Heat should not be less than 3 Athletes

that was going to be my next question. So if there are 42 runners you would have what?

amirkharkongor · May 13, 2022

CJ_London said:
that was going to be my next question. So if there are 42 runners you would have what?

6 Heats of 7 Players Each. Same formula.

CJ_London · May 13, 2022

Same formula.

OK so full calculation would be
number of runners:41
no of events 41/8=5.125 - round up to 6
no of lanes 41/6=6.8333 round up to 7

total runners 6*7=42?

if you round down the no of lanes you get a remainder
no of lanes 41/6=6.8333 round down to 6
remainder 41-(6*6)=5

but that means the number or events is now 7, not 6

and try that with say 37 or 44, the remainder is 2 - which is below your required minimum number of runners.

Need to log off now - I'll leave you to ponder

amirkharkongor · May 13, 2022

CJ_London said:
Same formula.

OK so full calculation would be
number of runners:41
no of events 41/8=5.125 - round up to 6
no of lanes 41/6=6.8333 round up to 7

total runners 6*7=42?

if you round down the no of lanes you get a remainder
no of lanes 41/6=6.8333 round down to 6
remainder 41-(6*6)=5

but that means the number or events is now 7, not 6

and try that with say 37 or 44, the remainder is 2 - which is below your required minimum number of runners.

Need to log off now - I'll leave you to ponder

41
Total No. of Heats	41/8=	6
Total No. of Athlete per Heat	41/6=	7
5 Heats of 7 Athletes and 1 Heat of 6 Players

37
Total No. of Heats	37/8=	5
Total No. of Athlete per Heat	37/5=	8
4 Heats of 8 Athletes and 1 Heat of 5 Players

44
Total No. of Heats	44/8=	6
Total No. of Athlete per Heat	44/5=	8
5 Heats of 8 Athletes and 1 Heat of 4 Players

Cronk · May 13, 2022

With the last example, instead of 5 heats of 8 and 1 heat of 4, wouldn't you prefer
4 heats of 7 and 2 heats of 8

Algorithm is divide number of contestants (C) by 8, round up to next whole number (N). Remainder (R) is N*8 - C
Have R heats of 7 and (N-R) heats of 8

MajP · May 13, 2022

See if this works. The only bad thing is that some of the associations will be in later heats only. However, in reality this is good. Your teammates will always run in consecutive heats. No waiting around

Code:

Public Sub AssignHeats(TheEvent As String)
  Const NumberOfLanes = 8
  Dim rs As DAO.Recordset
  Dim AthleteCount As Integer
  Dim Heats As Integer
  Dim HeatCounter As Integer
 
  Set rs = CurrentDb.OpenRecordset("Select * from AthleteEvent where event = '" & TheEvent & "' ORDER BY SchoolAssociation, AthleteID")
  If Not rs.EOF Then
   rs.MoveLast
   rs.MoveFirst
   AthleteCount = rs.RecordCount
   Heats = Int(AthleteCount / NumberOfLanes)
   'if there is a remainder then add one
   If AthleteCount Mod NumberOfLanes > 0 Then Heats = Heats + 1
  End If


  Do While Not rs.EOF
        HeatCounter = HeatCounter + 1
        rs.Edit
           rs!Heat = HeatCounter
        rs.Update
        rs.MoveNext
        If HeatCounter = Heats Then HeatCounter = 0
Loop


End Sub

Public Sub AssignHeatsTest()
  AssignHeats "100"
End Sub

Here is the results for 100
qry100 qry100

MajP · May 13, 2022

By Heat
qry100 qry100

Heat	SchoolAssociation	AthleteID
1	East Garo Hills District Sports Association	548
1	Mawkyrwat District Sports Association	815
1	Nongstoin District Sports Association	765
1	North Garo Hills District Association	701
1	Shillong Sports Association	831
1	South Garo Hills District Athletic Association	785
1	Tura District Athletic Association	671
1	West Jaintia Hills District Sports Association	730
2	East Garo Hills District Sports Association	552
2	Nongstoin District Sports Association	738
2	Nongstoin District Sports Association	766
2	Ri Bhoi District Sports Association	582
2	Shillong Sports Association	846
2	South West Garo Hills District Association	641
2	Tura District Athletic Association	672
2	West Jaintia Hills District Sports Association	731
3	East Garo Hills District Sports Association	556
3	Nongstoin District Sports Association	739
3	North Garo Hills District Association	679
3	Ri Bhoi District Sports Association	587
3	Shillong Sports Association	847
3	South West Garo Hills District Association	646
3	Tura District Athletic Association	675
4	East Garo Hills District Sports Association	559
4	Nongstoin District Sports Association	744
4	North Garo Hills District Association	680
4	Ri Bhoi District Sports Association	593
4	Shillong Sports Association	851
4	Tura District Athletic Association	654
4	West Jaintia Hills District Sports Association	708
5	East Jaintia Hills District Athletic Association	614
5	Nongstoin District Sports Association	745
5	North Garo Hills District Association	684
5	Ri Bhoi District Sports Association	594
5	Shillong Sports Association	853
5	Tura District Athletic Association	656
5	West Jaintia Hills District Sports Association	709
6	East Jaintia Hills District Athletic Association	615
6	Nongstoin District Sports Association	746
6	North Garo Hills District Association	685
6	Shillong Sports Association	823
6	South Garo Hills District Athletic Association	781
6	Tura District Athletic Association	658
6	West Jaintia Hills District Sports Association	713
7	Mawkyrwat District Sports Association	792
7	Nongstoin District Sports Association	758
7	North Garo Hills District Association	699
7	Shillong Sports Association	825
7	South Garo Hills District Athletic Association	782
7	Tura District Athletic Association	660
7	West Jaintia Hills District Sports Association	714
8	Mawkyrwat District Sports Association	799
8	Nongstoin District Sports Association	760
8	North Garo Hills District Association	700
8	Shillong Sports Association	830
8	South Garo Hills District Athletic Association	784
8	Tura District Athletic Association	670
8	West Jaintia Hills District Sports Association	729

Special Sort

Member

Attachments

I’m here to help

Immoderate Moderator

Member

Super Moderator

Member

AWF VIP

Super Moderator

Member

Super Moderator

Member

Super Moderator

Member

Super Moderator

Member

Super Moderator

Member

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Users who are viewing this thread