Selecting specific bands of records in a table for update
I have a table with several thousand records. There are 4 fields.
The key field is [ID] which auto-increments. The other fields will hold information for printing labels. These three fields will be purged regularly.
I am using table this as a scratch table to print out batches of labels.
I need to select the first 10 records in a table and apply an SQL update.
I may then need to be able to select records 11 - 35 and 36 - 200 and do the same.
Since the table can hold several thousand records, I would prefer to not step through this record by record.
I cannot use the key field because this field will only remain contiguous until someone deletes a record.
How can I select the first 10 records for such an update? Then select records 11-35 etc?
Thank you
The key field is [ID] which auto-increments. The other fields will hold information for printing labels. These three fields will be purged regularly.
I am using table this as a scratch table to print out batches of labels.
I need to select the first 10 records in a table and apply an SQL update.
I may then need to be able to select records 11 - 35 and 36 - 200 and do the same.
Since the table can hold several thousand records, I would prefer to not step through this record by record.
I cannot use the key field because this field will only remain contiguous until someone deletes a record.
How can I select the first 10 records for such an update? Then select records 11-35 etc?
Thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll wait some more for more answers. I have found Capricorn1's reply to be well suited for my needs, easily understood and implemented.
Curious what you found difficult in what I posted (did you try it)? The idea there is to use a query to number the rows sequentially, and use the derived row numbers to pinpoint the records that need to be updated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi - sorry for the slow reply.
<< Does your post rely on not allowing any deletions on the key field [ID]? >>
No - my remark about deletions was actually tangential. In many of our production applications, we don't allow users to delete records, period (for a variety of reasons). We might give them custom 'Delete' buttons which simply hide records as I described previously.
The rest of my comment not directly related to that remark, but rather compensates for missing IDs. It assumes a set of data that does have gaps in its autonumber field (from deletions, corrupted records, etc), and the query orders the data by the autonumber field, and gives you a sequential 'row number' without the gaps that might be present in the autonumber field.
Hope that helps explain :-)
<< Does your post rely on not allowing any deletions on the key field [ID]? >>
No - my remark about deletions was actually tangential. In many of our production applications, we don't allow users to delete records, period (for a variety of reasons). We might give them custom 'Delete' buttons which simply hide records as I described previously.
The rest of my comment not directly related to that remark, but rather compensates for missing IDs. It assumes a set of data that does have gaps in its autonumber field (from deletions, corrupted records, etc), and the query orders the data by the autonumber field, and gives you a sequential 'row number' without the gaps that might be present in the autonumber field.
Hope that helps explain :-)
ASKER
I continue to improve with the help that the experts give me. I value this greatly.
Thanks for all the help.
Thanks for all the help.
Open in new window