Custom Sort for sorting records like aaabbbccc to abcabcabc (1 Viewer)

Local time
Today, 22:32
Joined
Mar 23, 2022
Messages
45
Hi everyone,

I am new to Access Programming.

I am having data as

a
a
a
b
b
b
c
c
c

Now I want to sort the sort the record as

a
b
c
a
b
c
a
b
c

Anyone kindly hep me.

Thanks

Amir Kharkongor
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:02
Joined
May 7, 2009
Messages
19,169
you need a Temp table to do that. plus additional Autonumber field to original table.
plus a VBA. so it is not simple.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:02
Joined
May 7, 2009
Messages
19,169
what is you actual data? only a, b, c?
 
Local time
Today, 22:32
Joined
Mar 23, 2022
Messages
45
what is you actual data? only a, b, c?
something like

USA, 100
USA, 100
USA, 100
Australia, 100
Australia, 100
Australia, 100
China, 100
China, 100
China, 100

I want it sorted liked

Australia, 100
China, 100
USA, 100
Australia, 100
China, 100
USA, 100
Australia, 100
China, 100
USA, 100
 

June7

AWF VIP
Local time
Today, 09:02
Joined
Mar 9, 2014
Messages
5,423
Consider:

SELECT Table1.*
FROM Table1
ORDER BY DCount("*","Table1","Country='" & [Country] & "' AND ID<=" & [ID]), Table1.Country;

Be aware, domain aggregate function can cause slow performance with large dataset.
 
Last edited:

Eugene-LS

Registered User.
Local time
Today, 20:02
Joined
Dec 7, 2018
Messages
481

Attachments

  • Screenshot 2022-03-23 094537.png
    Screenshot 2022-03-23 094537.png
    11.4 KB · Views: 174
  • Test.zip
    18.8 KB · Views: 152

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:02
Joined
May 7, 2009
Messages
19,169
here's another flavour:

SELECT Table1.*
FROM Table1 ORDER BY
Dcount("1","Table1","Country='" & [Country] & "' And ID <= " & [ID]) & [Country]
 
Local time
Today, 22:32
Joined
Mar 23, 2022
Messages
45
here's another flavour:

SELECT Table1.*
FROM Table1 ORDER BY
Dcount("1","Table1","Country='" & [Country] & "' And ID <= " & [ID]) & [Country]
It works and finally I could proceed with my project.

Thank You very much.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Feb 19, 2002
Messages
42,971
You can't do this without an additional field to group the items. Your sample data is useless because it is showing identical records. Why would you have three copies of each record? Show us some realistic data and tell us what the missing field is. We might be able to help you to generate a "group" to keep each set of data together but this is not as easy as it sounds since relational database tables have no fixed order. So, if your table is large enough, it is quite possible that not all the records are in the order in which they were entered and if you don't have an autonumber to put them back in that order, you may not be able to group correctly at all.
 

June7

AWF VIP
Local time
Today, 09:02
Joined
Mar 9, 2014
Messages
5,423
@Pat Hartman, already demonstrated how this can be done without a grouping field in table. The grouping field is calculated. However, a unique identifier field is needed. Autonumber type serves and is easily added to table if not already there.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:02
Joined
Feb 19, 2002
Messages
42,971
You added the "additional" field which is an autonumber. With such bogus data, it is impossible to determine if the grouping will be rational which was the point I was trying to make.
 

Users who are viewing this thread

Top Bottom