Custom Sort for sorting records like aaabbbccc to abcabcabc

[amirkharkongor]

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]

you need a Temp table to do that. plus additional Autonumber field to original table.
plus a VBA. so it is not simple.

 

[amirkharkongor]

you need a Temp table to do that. plus additional Autonumber field to original table.
plus a VBA. so it is not simple.

Kindly help me.

 

[arnelgp]

what is you actual data? only a, b, c?

 

[amirkharkongor]

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]

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.

 

[Eugene-LS]

I want it sorted liked

Example below:

 

[arnelgp]

here's another flavour:

SELECT Table1.*
FROM Table1 ORDER BY
Dcount("1","Table1","Country='" & [Country] & "' And ID <= " & [ID]) & [Country]

 

[amirkharkongor]

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.

 

[amirkharkongor]

Thank You all for the help.

 

[Pat Hartman]

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]

@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]

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.