Sorting a Collection by a certain field

  • Thread starter Thread starter mpfohl
  • Start date Start date
M

mpfohl

I have a question about the best way to sort a collection in VB. My
actual use is complicated so I've simplified it with the following
fake example:

I have created a class called "Person" in VB in an MS Access program.
As part of my code, I create a collection (called 'myRoom') of several
'Person' instances. One of the Person's properties is "wealth".
Every "day" in the program I want to sort the group of people in
"myRoom" by "wealth" to find who the 5 richest people are to run a few
functions on those people.

I can think of a few ways to do this, which would be best?

1) Some how sort the collection by the 'wealth' property. As best as
I can tell, there is no built in 'sort collection' function, so this
may not be possible.

2) Create a virtual temporary table in the visual basic code (dao
probably?) have one field being the person's location in the
collection, the other field being their wealth. As I cycle through
each person, add them to that table, then at the end, sort the table
and return the top 5 records. Run the function on those 5 people

3) Same as above but with a permanent table: Have a permanent table
in access with the same fields as above, first delete any existing
records in this 'PersonSortTable', then cycle through each person and
add them to the list, then sort the table and create a query to kick
out the top 5. Run the function on those 5 people


I'm not sure which way would be best. Would creating a permanent
table, deleting and appending data create a large bloat problem in the
database? Does a table that exists only in the visual basic code make
there be less bloat? Is there another option I'm missing.

Thanks to all,
 
I would go with a permanent table until you’re satisfied with the solution
–you can always experiment later. If bloat is a concern, you can tell Access
to compact itself upon closing in Tools|Option|General|Compact on Close.
 
The problem is that I will cycle through each 'day' very quickly in
the code, so there isn't a chance to manually compact it. it could go
through 100 'days', which means deleting, appending, and sorting the
table 100 times. So I wondered if there is a more eligant solution
than a static table.
 
I imagine there's always an elegant solution...of which none of the following
is:

Alternatives to a table:
1. Use a sorted List Box (or any control that sorts)
2. Write a sorting function (one example:
http://www.ozgrid.com/VBA/sort-array.htm)
3. This is not an alternative to a table, but you could create the table to
be used for sorting in a separate MDB, link to it, and have the controlling
MDB or program compact it as necessary.

I guess those are simulated "days" -if they're not, 100 days is a long time
to keep an Access database open (meaning I don't think it was designed with
that kind of robustness in mind).
 
Back
Top