Access Cookbook - Ken Getz [128]
varMedian = CDate(varMedian)
End If
The code for an even number of rows is much simpler:
Else
' Odd number of records. Move to the middle record
' and return its value.
rstDomain.Move ((intRecords \ 2))
varMedian = rstDomain.Fields(strField)
End If
Finally, acbDMedian returns the median value to the calling procedure:
acbDMedian = varMedian
The median, like the average (or arithmetic mean), is known statistically as a measure of central tendency. In other words, both measures estimate the middle of a set of data. The mean represents the mathematical average value; the median represents the middle-most value. For many datasets, these two measures are the same or very close to each other. Sometimes, however, depending on how the data is distributed, the mean and median will report widely varying values. In these cases, many people favor the median as a better "average" than the mean.
Calculating the median requires sorting the dataset, so it can be rather slow on large datasets. Calculating the mean, however, doesn't require a sort, so it will always be faster to calculate the mean.
TIP
Microsoft Excel includes a Median function that you can call from Access using OLE Automation. Chapter 12 shows you how to do this. Because using OLE Automation with Excel requires starting a copy of Excel to do the calculation, you'll almost always find it simpler and faster to use the all-Access solution presented here.
6.5. Quickly Find a Record in a Linked Table
Problem
You like to use the ultra-fast Seek method to search for data in indexed fields in your table-type recordsets, but the Seek method won't work with linked tables because you can only open dynaset-type DAO recordsets against linked tables. You can use the Find methods to search for data in these types of recordsets, but Find is much slower at finding data than Seek. Is there any way to use the Seek method on linked tables?
Solution
The Seek method works only on table-type recordsets, so you can't perform seeks on linked tables. However, there's no reason why you can't open the source database that contains the linked table and perform the seek operation there. This solution shows you how to do this.
To use the Seek method on external tables, follow these steps:
Use the OpenDatabase method to open the source database that contains the linked table. For example, in the event procedure attached to the cmdSeek command button on the sample form, frmSeekExternal, you'll find the following code:
Set wrk = DBEngine.Workspaces(0)
' Directly open the external database. It will be opened
' nonexclusively, read-write, and with type = Access.
Set dbExternal = _
wrk.OpenDatabase(acbGetLinkPath("tblCustomer"),, False, False, "")
Create a table-type recordset based on the source table. If you renamed the table when you linked to it, make sure you use the name used in the source database. The sample form uses this code:
' Create a table-type recordset based on the external table.
Set rstCustomer = dbExternal.OpenRecordset("tblCustomer", dbOpenTable)
Set an index and perform the seek operation, as in this code behind the sample form:
' This index consists of last and first names.
rstCustomer.Index = "FullName"
' Perform the seek and then check if the record was found.
rstCustomer.Seek "=", ctlLName.Value, ctlFName.Value
Any time you perform a seek or a find, you must next check to see if the operation was successful. You do this using the NoMatch property of the recordset. For example, on the sample form, you'll find the following code:
strMsg = "The record for " & ctlFName & ctlLName & " was"
If Not rstCustomer.NoMatch Then
strMsg = strMsg & " found!" & vbCrLf & vbCrLf
strMsg = strMsg & "Customer# = " & rstCustomer![Customer#]
MsgBox strMsg, vbOKOnly + vbInformation, "External Seek"
Else
strMsg = strMsg & " not found!"
MsgBox strMsg, vbOKOnly + vbCritical, "External Seek"
End If
Close the recordset and the external database. The sample form uses this code:
rstCustomer.Close