Report Topics| Module Code and APIs | General Issues

 

How to create an Auto Number For Each Row of Your Query.

You 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. To create a query which has an auto number assigned field for each row of output you use a sub-query in the auto number field. A sub-query is a query which is imbedded within your target query or report record source. They can be used to limited data in a where condition, or act as a field's data source. To create an auto number field, we use a sub-query as a field to count the number of records which occurred in underlying table or query before the current row is reached. Critical to creating the auto number sub-query is that the table or query you are using as a source must contain a unique index and this field must be included in your auto number query. As an example in an order's report, usually the order number would be a unique field used in the query or recordset. Example: To list all orders in the table, "tblOrders" and auto number the output rows, you would enter the following ina field of the orders query: RowNum: (Select Count (*) FROM [tblOrders] as Temp WHERE [Temp].[OrdNum] < [tblOrders].[OrdNum])+1 If 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 sub-query, so that temp recordset generated by the sub-query has the same result set as your main query.  As an example, if you wanted the output 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

  Back to the Tips List

 

How to create a running sum query

The running sum query is a simple variant on an auto number query where the query or recordset includes a sub-query as a field to calculate the running sum. As with an auto number query, to create a running sum requires that your query have at least one field which has a unique value for each row. The running sum simply uses the sub-query to sum all the values in rows where the unique field value is are less than or equal to the value of the current field. Here's an example: RunningSum: (Select Sum (OrderTotal) FROM [Orders] as Temp WHERE [Temp].[OrderID] <= [Orders].[OrderID])

  Back to the Tips List

 

Multi-User Query and Record Set 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: Wrap all record updates and additions in transactions to manage errors and record locking conflicts. (See the tip on managing bulk updates with transactions in this section.) Also add retry logic to your error handling code in the event of record locking conflicts. If you are using an Access database for your backend "server" database, enhance the speed of your queries or record sets accessed from a server by reducing or eliminating any calculations or actions taken on the data in the query itself. Rather create code in your forms, reports and methods (functions) to manipulate the data once it is fetched from the server. This will reduce network traffic by reducing the row by row processing required as data is fetched from the server and calculations applied by Access at the workstation if the query retains calculations embedded in it directly, and allow the Jet engine's Rushmore technology to fetch the data the fastest. (This is not a primary issue if you are using an ODBC database connection with stored procedure processing.) Open any queries or record sets where you are not going to do a find with in the record set a snapshot mode, or if you are doing a find in "dbReadOnly" mode to reduce the record locks imposed on the data, which may result in record locking conflicts for other users. In the same vein, you may want to create read only versions of forms for user's who do not need to edit data such as managers etc. These forms will open faster and reduce locking conflicts. Where possible, make sure that any fields which are used to limit data in the record set (i.e. the Where clause) are based on fields that are indexed. This will reduce table scans and reduce record locks. Create and use straight data entry forms (additions only) for addition of new records, rather than using a standard form with full edit capability. This will reduce record locking conflicts. In a multi-user environment, after any operation which updates records, such as record additions or edits, add the command "DBEngine.Idle" which forces the Jet engine to flush any delayed ("lazy") writes to disk, thereby releasing record locks. Keep static look up table data, which is not normally updated, such as state abbreviations, as a table in the workstation client database, this will enhance speed and reduce network traffic. Review and lower the number of indexes used on your tables and in your queries to the smallest number of indexes possible.  At the same time, in your queries try to include use of fields that have a unique index in the WHERE clause when querying large amounts of data. If you are placing parameters and conditions on the record set, place them on the table and field of the "one" side of a "one to many" relationship rather than on the "many" side. Avoid where possible, especially on indexed fields and fields used as part of a WHERE clause, using custom functions or build in Access functions in the query.  When custom functions or Access functions are used on those fields, the Access Jet database engine can not use the indexes available on the fields in question to speed the return of the data; it simply can't "see" the field names and therefore use of the index is lost.  Rather as with all calculations try to move them to the form or report which is operating on or displaying the data.

  Back to the Tips List

 

Enhancing the Speed of Large Bulk Record Update Actions

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 record set 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.

  Back to the Tips List

 

How to open a parameterized stored procedure as a Record Set using ADO

If you haven't worked with ADO or SQL server much in the Web and ASP world, you may find the Access  help file is short of examples to get you acquainted with some basic operations of working with ADO record sets if you are using the MS Data Engine (MSDE or SQL Server 7, 2000). One of these basic operations is opening a parameterized record set in code. To open a parameterized record set, you execute the stored procedure while assigning it to an ADO record set object. Here's an example of opening the "Sales By Year " stored procedure of the Northwind.adp sample db, via VBA code. Note that the code below is optimized for ADO 2.1, which is the least common denominator for ADO versions and the default for Access 2000 (with later versions you can use the Parameters.refresh method rather than having to specifically name and address each parameter). Dim cnn As Connection Dim rstCom As ADODB.Command Dim rstSales As ADODB.Recordset Dim objPrm1 As ADODB.Parameter Dim objPrm2 As ADODB.Parameter Set cnn = CurrentProject.Connection cnn.CursorLocation = adUseClient Set rstSales = New ADODB.Recordset Set rstCom = New ADODB.Command rstCom.ActiveConnection = cnn rstCom.CommandText = """Sales by Year""" rstCom.CommandType = adCmdStoredProc 'Create parameters for your command object that 'match the stored procedure parameters. Set objPrm1 = rstCom.CreateParameter("@beginning_date", _ adDBDate, adParamInput) rstCom.Parameters.Append objPrm1 objPrm1.Value = "1/1/97" Set objPrm2 = rstCom.CreateParameter("@ending_date", _ adDBDate, adParamInput) rstCom.Parameters.Append objPrm2 objPrm2.Value = "12/31/97" Set rstSales = rstCom.Execute 'Now you can work with the Sales By Year 'here using the rstSales object rstSales.Close Set rstCom = Nothing cnn.Close

  Back to the Tips List

 

Calculating Statistical Percentiles in a Query (or Report)

Access does not contain many common statistical functions for use in queries or reports.  One of the common statistical measures used on data sets is to calculate the median and percentiles.  There are two ways to arrive at these numbers; Option 1 is to calculate the value using standardized statistical table values which assume a normal distribution, Option 2 is to calculate the value using the observed data points in a specific data set. 1) You can calculate a percentile using a standardized statistical Z-table value using Access' built Avg and StDev functions to calculate the mean and the standard deviation.  The percentile formula is: Percentile = Mean (Avg) Value + (Standard Deviation * Z-table value) For common percentiles, the z-table values are: 75th percentile = .674 25th percentile = -.674 This method assumes the data in your record set to be normally distributed.  In a normally distributed data set, the mean = the median.  However, in many data sets, the mean does not equal the median because the data is skewed toward the high or low end and the result is that the mean does not equal the median.  So you have to use a different method to extract actual percentiles from the data set. 2) The second method to calculate percentiles is to determine the percentile values from the specific data you have in the record set.  However since Access doesn't have a Percentile or Median function (median = 50th percentile), you can use the code VBA code below to calculate the percentiles for a given data set.  (The original version of this code, preserved with the author's comments, was posted on the newsgroup microsoft.public.access.queries by "Tom" in November 2001  It is updated here to cover both DAO and ADO data methods, to shorten looping through the target record set, and to add the option to filter the record set.) Note this code uses DAO data access  methods, with the ADO methods commented out, if you use ADO, simply uncomment those lines and comment out the DAO lines. Public Function Percentile(fldName As String, _ tblName As String, p As Double, _ Optional strWHERE As String = "") _ As Double 'Note tblName can also be the name of a query or view 'ADO 'Dim cnn As ADODB.Connection 'Dim rst As ADODB.Recordset 'DAO Dim Cdb As Database Dim rst As Recordset 'Other Vars Dim break_pt As Double Dim sqlSort As String Dim low_obs As Long, high_obs As Long Dim r1 As Double, r2 As Double, x As Double Dim N As Long Dim recno As Long 'VERIFY VALID PERCENTILE (0-100) WAS GIVEN If (p <= 0 Or p >= 100) Then Percentile = -555555555 'Something to stick out! Exit Function End If 'ENSURE DESIRED DATA IS SORTED If Len(strWHERE) < 1 Then sqlSort = "SELECT [" & fldName & "] " & _ "FROM [" & tblName & "] " & _ "ORDER BY [" & fldName & "]" Else sqlSort = "SELECT [" & fldName & "] " & _ "FROM [" & tblName & "] WHERE " & _ strWHERE & _ " ORDER BY [" & fldName & "]" End If 'ADO 'Set cnn = CurrentProject.Connection 'Set rst = New ADODB.Recordset 'rst.Open sqlSort, cnn, adOpenStatic, adLockReadOnly, 1 'DAO Set Cdb = CurrentDb() Set rst = Cdb.OpenRecordset(sqlSort, dbOpenSnapshot) rst.MoveLast 'How many observatons? For example, N=12 N = rst.RecordCount 'Which observation would, theoretically, _ be the pTH "true" percentile. 'e.g., for 25th percentile would be _ the 0.25*(12+1)=3.25th observation break_pt = (p / 100) * (N + 1) '3.25 = (25/100)*(12+1) 'There's 2 special extreme cases we need to worry about! 'small sample for small percentile If break_pt < 1 Then break_pt = 1 'small sample for large percentile If break_pt > N Then break_pt = N 'But since there's no such thing as a _ 3.25th observation, we estimate it _ 'somewhere between the 3rd and 4th observations. _ It'll be approximately: p = r1*low_obs + r2*high_obs low_obs = Int(break_pt) '3 = int(3.25) high_obs = low_obs + 1 '4 = 3 + 1 'Now, we have to interprolate between the "boundaries" r1 = high_obs - break_pt '0.75 = 4 - 3.25 r2 = 1 - r1 '0.25 = 1 - 0.75 'Since we have determined the needed _ observations and their weights we can move to the _ projected low observation and loop _ through the record set until we reach the _ high observation to calc the resulting percentile 'DAO, where absolution position is 0 based rst.AbsolutePosition = low_obs - 1: recno = low_obs - 1 'ADO where absolution position is 1 based 'rst.AbsolutePosition = low_obs: recno = low_obs - 1 DoEvents Do Until rst.EOF recno = recno + 1 If recno = low_obs Then x = r1 * rst(0) If recno = high_obs Then x = x + r2 * rst(0) Exit Do End If rst.MoveNext Loop 'We now have our percentile! Percentile = x rst.Close Set rst = Nothing 'DAO Set Cdb = Nothing 'ADO 'Set Conn = Nothing End Function Public Sub Test() Dim strMsg As String strMsg = "25th = " & Percentile("YourField", "YourTable", 25) _ & vbNewLine & _ "Median = " & Percentile("YourField", "YourTable", 50) _ & vbNewLine & _ "75th = " & Percentile("YourField", "YourField", 75) MsgBox strMsg End Sub

Back to the Tips List