General
How to create an Auto Number For Each Row of Your Query's Ouput Multi-User Environment Query and Recordset Issues Enhancing the Speed of Large Bulk Updates to RecordSets
|
![]() ![]() |
How to create an Auto Number For Each Row of Your Query. |
Your application may need to produce a report or query which has each row of output numbered to enhance report readability or to print only every X row of output. You can easily create a query which has an auto number assigned field for each row of output by using a subquery.
A subquery is a query which is imbeded within your target query or report recordsource. They are often used to limit data to a subset of a standard recordset, find dublicate records or another action. To create an auto number field, we use a subquery as a field in our query to count the number of records which occured in the output of the target query before the current row. Critical to creating the auto number subquery is basing the subquery a virtual copy of a unique index of the main query's recordset and include the same parameters for output that the main query does. As an example in an order's report, usually the order number would be a unique field in the target query or recordset. If you were listing all orders in the table, "tblOrders" and wanted to auto number the output rows of such an order query, you would enter the following in an output field of the orders query: RowNum: (Select Count (*) FROM [tblOrders] as Temp WHERE [Temp].[OrdNum] < [tblOrders].[OrdNum])+1If you need to constrain your query's output (as is usually the case,) to a specific set of records, you must add the same constraints to your auto number subquery, so that the temporary virtual recordset generated by the subquery has the same result set as your main query. So if as an example you wanted the output or target query to only list orders between a specific "StartDate" and "EndDate" you would also add the same parameters to the subquery previously shown, so that your subquery in the field would now read: RowNum: (Select Count (*) FROM [tblOrders] as Temp WHERE ((Temp.[OrdDate] BETWEEN [Startdate] AND [EndDate] ) AND ([Temp].[OrdNum] < [tblOrders].[OrdNum])))+1 This technique would work on any unique index contained in your main query be it either a numbered index, or alpha index. |
![]() ![]() |
Mutli-User Query and Recordset Issues. |
When moving your database application from a development or single user environment to a multi-user environment, there are a number of issues to consider to enhance the speed of operation, data integrity and reduce record locking conflicts. Here's some general tips:
|
![]() ![]() |
Enhancing the Speed of Large Bulk Update Recordsets |
In a client server environment, when updating a large number of records say 2,000 or more, under transaction management in your application, you can speed up your bulk update, (and eliminate the possibility of
"out of memory" errors) by breaking down the update into smaller updates of a portion of the records.
This is because under transaction management, all the data is maintained in memory until it is committed to disk by a "CommitTrans" command. If the recordset is huge, this will require significant amounts of memory, and likely heavy use of your workstation's swap file for virtual memory memory allocation. By breaking down the update into smaller transactions you can maintain the whole process in memory which enhances the speed of the update. |