Renumbering a list

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

Guest

Each row in a list of items has a number. 1, 2, 3, 4, and so on. The number
is used to sort the list in a report and the user can change the number to
affect the outcome of the sort.

Now a record (number 2, say) is deleted.

How do I design an update query to renumber the rows (1 stays 1, 2 stays 2,
4 becomes 3, etc.)?
 
Hi Rod,

Unless there's considerably more happening with your records than you
mention in your post, I suggest you simply ignore the fact that a record is
deleted. The remaining records will still sort perfectly well. And if (as
is likely, but you haven't described it), the default sort number is set
when a new record is added by adding 1 to the current maximum sortorder
number (eg, via a DMax expression), nothing will break if a number within
the sequence is deleted.

If your users can change the sortorder number directly in a form, and they
feel compelled to do so to cope with a gap in the sortorder number sequence,
then let them do so manually. A little education (that 1,3,4, ... sorts
exactly the same as 1,2,3,4, ... when record 2 no longer exists) coupled
with a lot of wasted time and effort in reassigning sortorder numbers when
it isn't needed, could well convince them (and you) that this isn't worth
devoting a lot of programming (or update query designing) effort to.

HTH,

Rob
 
Hey Rob

The numbered items are line-items on a quotation and, sadly, are used in the
quotation. It would therefore give the impression that my sales staff could
not count not to renumber the rows...

That said, I have come up with a solution - an update query which uses
DCount. I DCount all rows within the original data list which have a
SortNumber < SortNumber of the current row. Add 1 and you get a sorted,
renumbered list:

DCount("Field","Table","SortNumber = " & SortNumber) +1
 
That *MAY* not work. Assume the records will be seen in the sequence of
values < 6, 4, 2> . Applied to the first record, that will give <3*, 4,
2> where I use the * to indicate the updated record(s) up to now. Next,
with the second record, that will *now* gives the updated-in-progress
values <3*, 3*, 2> and, after the last record updated: <3*, 3*, 1*> and
the result is wrong.

You may have to re-rank the records based on their values to be ranked, OR
to be sure the update process always occur from the lowest value to the
highest (in our example, the sequence being on <2, 4, 6>, that will lead to
a correct result).



Vanderghast, Access MVP
 
Hi Michael

Yeah, I realised the dangers. Fortunately, from a GUI perspective, I can
force the update to run after every row update, so there is never more than
one record out of sequence. Also, the users don't actually change the number
- this is done programatically when they hit a button, which allows me to
manage the process.

Thanks for the warning, though.
 
You do realize that you can set line numbers in a report that are one-up in
the order that records are displayed/printed in a report.

So you could have
sort Number (not printed/not visible), line num (printed)
01 - 1
05 - 2
06 - 3
22 - 4

You just add a control to the report
Set its source to =1
And its running sum to Overall (or over group depending on your need)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Yes, I do, however these values need to be manipulated within the GUI.
Numbering, running sums and that sort of this is relatively simple within a
report, but not within the raw query.
 
Back
Top