Use VBA to sort ??

[greaseman]

I know how to sort using queries, but am interested in how to programatically sort, using VBA. I did a bunch of searches on the forum, but didn't find anything.

Does anyone have a suggestion or a small sample of how to sort one table's fields into antoher table?

Thanks!

 

[KenHigg]

Hum... Not sure what you're leading into here. I assume you want your records to appear in a table in a certain order? If this is the case, my answer would be to simply sort them on the fly with a query as you need them. Why do you need them to appear in a certain order in a table?

 

[greaseman]

It'll make it easier to export the data in a user-requested arrangement. Plus, I only want to have a small code module that'll take my input table, sort certain fields within the code and then "spit out" an output table all nicely sorted and arranged.

I wouldn't think I need to have queries, code, queries, more code, etc. I'm just looking for a small sample of VBA code that shows how to do a sort.

Can you help??

 

[KenHigg]

I suppose you could do a pc code to execute a make table query that had the records sorted. Does this sound like something you could use?

 

[Bat17]

try a bubble sort
http://www.access-programmers.co.uk/forums/showthread.php?t=26439&highlight=bubble+sort

HTH

Peter

 

[ice-9]

try a bubble sort
http://www.access-programmers.co.uk/forums/showthread.php?t=26439&highlight=bubble+sort

HTH

Peter

try quicksort
its better then bubble.
but a littlebit more difficult to understand

 

[workmad3]

use the access sorting, as it will already be optimised(probably using an optimised heap sort or something similar) and will be running as win32 native compiled code rather than the mish-mash of compiled and interpreted code that is VBA. Implementing your own sorting algorithm on top of access will be horribly inefficient. Also never even consider using a bubble sort on something as potentially large as a database(horribly inefficient on large data sets). Quicksort will give varying reults on this, as a database table could already be sorted(the worst possible case for quicksort, makes it as inefficient as bubble sort). A merge sort would probably be alright, but it is very space inefficient, so using it on data sets that get too large will take up a lot of memory. There are so many variables to consider for a sort, its best to leave it to access as it will have a sort optimised to its internal data structure which will be more efficient than anything coded in VBA

 

[ice-9]

(the worst possible case for quicksort, makes it as inefficient as bubble sort).

This is true!

 

[workmad3]

Uni module on algorithms last year spent far too much time on sorting algorithms

Of course, if you want some fun, you could always write an optimised sorter using entirely assmbler code wouldnt be much good for access, but you would certainly learn why its best to leave writing optimised sorts to other people(did this for a bubble sort in my first year of uni. took about 200 lines of assembler)

 

[greaseman]

Thanks to all of you who responded! I truly appreciate your ideas and suggestions. I'll give them a go and see what "sorts out." And, actually, this was only for a small table of about 3,000 records, so optimization wasn't a concern.

What I ended up doing in the meantime was to zero-pad my desired sorting fields (they were alpha fields) with a small VBA module, and then put in some SQL code into my VBA module. It took a couple of turns, but it looks OK.

Again, to all who replied, thanks! Wouldn't it be nice if Mr. Gates would incorporate the ideas from this forum?????

 

[workmad3]

Again, would advise you against using vba to implement a sorting algorithm for database records. If you have all the fields that you want to sort on, then use an SQL query to sort on them, and I expect it will greatly outperform any VBA implemented sorting method.