Alternating Sort

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table containing two specific fields named "Category" & "Index".
"Category" contains values ranging from 1 to 3 and "Index" contains a
sequence of numbers ranging from 1 to however many records are in each
category. I want to open a recordset that is sorted as such. . . [Cat=1
Idx=1], [Cat=2 Idx=1], [Cat=3 Idx=1], [Cat=1 Idx=2] and so on. My ultimate
goal is to use the recordset to update "Index" to range from 1 to total
record count after sorting as described. Any advise would be greatly
appreciated.
 
In query design view, the sorting works left-to-right, so you will have to
show the Category field on the left (without sorting), and add it again to
the right of the Index field (with sorting.)

1. Create a query that uses this table.

2. Drag the Category field into the grid.

3. Drag the Index field into the grid.
Set Ascending in the Sorting row.

4. Drag the Category field into the grid again.
Set Ascending in the Sorting row.
Uncheck the Show box.
 
sort by the Index field first, then by the Category field. whether you're
opening a recordset in VBA, or using a SQL statement in a form's
RecordSource property, try

SELECT Category, Index
FROM TableName
ORDER BY Index, Category;

btw, if you really have a field in your table named Index, suggest you name
it. "Index" is a Reserved word in Access97 thru Access2003, and also a
Reserved word in Jet 4.0, and as such it should not be used as a name of
anything that *you* name in a database. see
http://home.att.net/~california.db/tips.html#aTip5 for more information.

hth
 
In trying to cut corners by making my post generic, I now see I was
misleading. My initial Idx values are not actually "1 to however many records
are in each category." The actual values of Idx are randomized numbers.
therefore the first Idx value in Cat-1 vs the first Idx value in Cat-2 are
not equal values. Therefore the sort-by Idx then Cat suggestions will not
work. Sorry for the misleading information.
 
Back
Top