Update query to set value based on position in recordset

[Robert Schripsema]

We have a table with a "SortPos" function that is used by an
application to display records in a particular order on a form.
Normally, you'll have a clean, sequential list of numbers (1,2,3,4,5,
etc) in the various records in this table. Occasionally, a gap will
show up -- usually because a record was deleted, or a new record was
added to the end with an out of sequence record number (e.g., 99, when
there are only 50 records). Or, a SortPos value will be duplicated,
such that we'll have a sequence like 1,2,3,4,4,5,6,6,6,7,9, etc.)

I want to write an update query or process within Access that will
allow me to re-set those SortPos values to a new, clean set of
sequential numbers. I'm envisioning an update query that will retrieve
all the records in the table sorted by the CURRENT SortPos value, then
update the value of the SortPos field to the records position as
returned in the recordset. Something like this:

Update MyTable set SortPos = #RecordPos order by SortPos << clearly
this won't work,

To make something like this work, I need two things (at least):

1) A way to update the records in a particular order -- setting a sort
order on the Update statement
2) A way to access the current records position in the recordset that
is returned from #1.

Any of you geniuses out there have an idea?

Rob Schripsema

 

[John W. Vinson]

We have a table with a "SortPos" function that is used by an
application to display records in a particular order on a form.
Normally, you'll have a clean, sequential list of numbers (1,2,3,4,5,
etc) in the various records in this table. Occasionally, a gap will
show up -- usually because a record was deleted, or a new record was
added to the end with an out of sequence record number (e.g., 99, when
there are only 50 records). Or, a SortPos value will be duplicated,
such that we'll have a sequence like 1,2,3,4,4,5,6,6,6,7,9, etc.)

I want to write an update query or process within Access that will
allow me to re-set those SortPos values to a new, clean set of
sequential numbers. I'm envisioning an update query that will retrieve
all the records in the table sorted by the CURRENT SortPos value, then
update the value of the SortPos field to the records position as
returned in the recordset. Something like this:

Update MyTable set SortPos = #RecordPos order by SortPos << clearly
this won't work,

To make something like this work, I need two things (at least):

1) A way to update the records in a particular order -- setting a sort
order on the Update statement
2) A way to access the current records position in the recordset that
is returned from #1.

Any of you geniuses out there have an idea?

Rob Schripsema

Do you have some way to break ties, such as a Primary Key field in the table?
If so you can run an Update query updating SortPos to

=DCount("*", "[tablename]", "[SortPos] <= " & [SortPos] & " AND [PrimaryKey]
<= " & [PrimaryKey])

This will count the number of records in the table that are "below" the
current sortpos value - breaking ties such as your 4, 4 using the primary key
- and replace the sortpos value with that count.

TEST THIS on a copy of the table first of course!!

 

[geoleo68]

Probably you will eventually find a way to do it with one single update
query, but this what I would do.

1) Copy the structure of your file into a temp table and change the value of
the temp SortPos field to "Autonumeric".

2) do an INSERT or SELECT INTO query to pass on to the temp table all the
data EXCEPT the SortPos field wich will automatically be generated by the
autonumeric field. Don't forget to sort your records by your current SortPos
field in the select into query.

3) Delete all fields from your actual table.

4) SELECT INTO your actual table all data from the temp table (trying to re
set the autonumeric field to numeric won't work once the table has data in it)

5) DROP the temp table.

Put this 5 steps into a single macro and it will ease your work everytime
you need to update your sort sequence.

Hope it works

geoleo68