Special Sort (1 Viewer)

Local time
Tomorrow, 02:51
Joined
Mar 23, 2022
Messages
45
Hi Friends,

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

the query output is

BIbNoAthleteNameSchoolAssociation
046ANIMA ARENGHEast Garo Hills District Sports Association
248Wanda LyngkhoiNongstoin District Sports Association
246Tiewlari RynshiangNongstoin District Sports Association
190SILTE G. MOMINNorth Garo Hills District Association
073SHIDALIN RONGPIRi Bhoi District Sports Association
025Feliscita RynjahShillong Sports Association
024Arkador Lyngdoh NongbriShillong Sports Association
162MIRIME D. SANGMA Tura District Athletic Association
161RINGRINGCHI K. SANGMATura District Athletic Association
320BEAUTIFY SHYLLAWest Jaintia Hills District Sports Association
220IAISHAH DKHARWest Jaintia Hills District Sports Association

But I want to sort it in the following format


BIbNoAthleteNameSchoolAssociation
046ANIMA ARENGHEast Garo Hills District Sports Association
248Wanda LyngkhoiNongstoin District Sports Association
190SILTE G. MOMINNorth Garo Hills District Association
073SHIDALIN RONGPIRi Bhoi District Sports Association
025Feliscita RynjahShillong Sports Association
162MIRIME D. SANGMA Tura District Athletic Association
320BEAUTIFY SHYLLAWest Jaintia Hills District Sports Association
246Tiewlari RynshiangNongstoin District Sports Association
024Arkador Lyngdoh NongbriShillong Sports Association
161RINGRINGCHI K. SANGMATura District Athletic Association
220IAISHAH DKHARWest Jaintia Hills District Sports Association
 

Attachments

  • test(2).accdb
    1.3 MB · Views: 145

theDBguy

I’m here to help
Staff member
Local time
Today, 14:21
Joined
Oct 29, 2018
Messages
21,553
Consider adding a sort order column in your table.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:21
Joined
Feb 28, 2001
Messages
27,332
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 ;
 
Local time
Tomorrow, 02:51
Joined
Mar 23, 2022
Messages
45
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.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Feb 19, 2013
Messages
16,683
how does this differ from your other thread?
 
Local time
Tomorrow, 02:51
Joined
Mar 23, 2022
Messages
45
how does this differ from your other thread?
Suppose we have the following entries for 100meters Track Event.
BIbNoAthleteNameSchoolAssociation
046ANIMA ARENGHEast Garo Hills District Sports Association
248Wanda LyngkhoiNongstoin District Sports Association
246Tiewlari RynshiangNongstoin District Sports Association
190SILTE G. MOMINNorth Garo Hills District Association
073SHIDALIN RONGPIRi Bhoi District Sports Association
025Feliscita RynjahShillong Sports Association
024Arkador Lyngdoh NongbriShillong Sports Association
162MIRIME D. SANGMATura District Athletic Association
161RINGRINGCHI K. SANGMATura District Athletic Association
320BEAUTIFY SHYLLAWest Jaintia Hills District Sports Association
220IAISHAH DKHARWest 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 :
BIbNoAthleteNameSchoolAssociationHeats
046ANIMA ARENGHEast Garo Hills District Sports Association1
246Tiewlari RynshiangNongstoin District Sports Association1
190SILTE G. MOMINNorth Garo Hills District Association1
073SHIDALIN RONGPIRi Bhoi District Sports Association1
024Arkador Lyngdoh NongbriShillong Sports Association1
162MIRIME D. SANGMA Tura District Athletic Association1
320BEAUTIFY SHYLLAWest Jaintia Hills District Sports Association 2
248Wanda LyngkhoiNongstoin District Sports Association2
025Feliscita RynjahShillong Sports Association2
161RINGRINGCHI K. SANGMATura District Athletic Association2
220IAISHAH DKHARWest Jaintia Hills District Sports Association 2
 

Minty

AWF VIP
Local time
Today, 22:21
Joined
Jul 26, 2013
Messages
10,378
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

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Feb 19, 2013
Messages
16,683
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?
 
Local time
Tomorrow, 02:51
Joined
Mar 23, 2022
Messages
45
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
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Feb 19, 2013
Messages
16,683
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
 
Local time
Tomorrow, 02:51
Joined
Mar 23, 2022
Messages
45
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

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Feb 19, 2013
Messages
16,683
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)
 
Last edited:
Local time
Tomorrow, 02:51
Joined
Mar 23, 2022
Messages
45
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

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Feb 19, 2013
Messages
16,683
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?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Feb 19, 2013
Messages
16,683
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
 
Local time
Tomorrow, 02:51
Joined
Mar 23, 2022
Messages
45
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 Heats41/8=
6​
Total No. of Athlete per Heat41/6=
7​
5 Heats of 7 Athletes and 1 Heat of 6 Players
37​
Total No. of Heats37/8=
5​
Total No. of Athlete per Heat37/5=
8​
4 Heats of 8 Athletes and 1 Heat of 5 Players
44​
Total No. of Heats44/8=
6​
Total No. of Athlete per Heat44/5=
8​
5 Heats of 8 Athletes and 1 Heat of 4 Players
 

Cronk

Registered User.
Local time
Tomorrow, 07:21
Joined
Jul 4, 2013
Messages
2,774
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

You've got your good things, and you've got mine.
Local time
Today, 17:21
Joined
May 21, 2018
Messages
8,609
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
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:21
Joined
May 21, 2018
Messages
8,609
By Heat
qry100 qry100

HeatSchoolAssociationAthleteID
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​
 

Users who are viewing this thread

Top Bottom