function used in query repeats for each query value
Hi Lance,
I'm not a programmer by trade, so I'm not really following what you are
suggesting. You mentioned the table structure sounding flawed. Here is an
example of the basic table layout which of course repeats for many more
records and foundations. There really isn't much too it.
Sheet1 ID Foundation Recipient Amount
79 Citigroup Foundation, NY American School for the Deaf, CT
$15,000.00
80 Citigroup Foundation, NY Asylum Hill Congregational Church, CT
$50,000.00
81 Citigroup Foundation, NY Ballet School of Stamford, CT $15,000.00
82 Citigroup Foundation, NY Boys and Girls Clubs of Hartford, CT
$10,000.00
83 Citigroup Foundation, NY Bridge Family Center, CT $16,500.00
The current query takes the foundation field as the grouping field and the
median function requires that I supply it with the table name (sheet1),
calculation field (amount), grouping field (foundation), and the current
value in of the grouping field (sheet1.foundation).
This produces:
Query2 Foundation Median
Citigroup Foundation, NY $20,000.00
Dibner Fund, Inc., The, CT $25,000.00
But at the cost of rerunning the function for every row.
So, if I use what you are suggesting, what do I need to differently?
Thanks a lot.
Michael
Quote:
> Without digging too deep in it it looks like a design problem with the
> tables. If you have groups and subordinate records then run code against
> the group using the sub records. Then join them back with a query. This
is
> much faster (subsecond with 65 rows).
> Hope it helps.
> > I have written a function that calculates the median based on a grouping
> > level.
> > The way I call the function from the query causes it to run for every
row
> > rather than every group which slows down performance to a crawl.
> > Essentially, for 3 groups I am running 65 queries rather than 3.
> > Here is the SQL statement I use for the query (sheet1!Foundation is a
> value
> > in the query that is used to create the SQL statement to get the group
of
> > records):
> > SELECT Sheet1.Foundation,
> > GetMedian("Sheet1","Amount","Foundation",sheet1!Foundation) AS Median
> > FROM Sheet1
> > Is there a way to have the query call the function once for each
grouping
> > level rather than for member in the group?
> > I've included the function code for reference. Incidently, when run
> without
> > a grouping factor the code is run once which is leading me to believe
that
> > the difference is including a query value in the function call.
> > Thanks for any help you can provide.
> > Michael
> > Function GetMedian(tblName As String, tblValue As String, _
> > Optional tblGrp As String, Optional tblGrpVal As String) As Currency
> > Dim varData As Variant
> > Dim introws As Integer
> > Dim curMedian As Double
> > Dim intDim As Integer
> > Dim varMed() As Double
> > Dim strSQL As String
> > 'Dim strtblGrpVal As String
> > Dim objXL As Excel.Application
> > Dim rst As ADODB.Recordset
> > Set rst = New ADODB.Recordset
> > rst.CursorLocation = adUseClient
> > If tblGrp = "" Then 'No group
> > strSQL = "Select " & tblName & "." & tblValue & " from " & tblName &
> ""
> > rst.Open strSQL, CurrentProject.Connection, adOpenKeyset,
> > adLockOptimistic, adCmdText
> > introws = rst.RecordCount
> > varData = rst.GetRows(introws, Fields:=Array(tblValue))
> > intDim = introws - 1
> > 'Debug.Print varData(0, 3)
> > Set objXL = CreateObject("Excel.Application")
> > ReDim varMed(0 To intDim) As Double
> > For k = 0 To introws - 1
> > varMed(k) = varData(0, k)
> > Next
> > curMedian = Excel.WorksheetFunction.Median(varMed())
> > GetMedian = curMedian
> > Else 'Median value based on group
> > strSQL = "Select " & tblName & "." & tblValue & " from " & tblName &
"
> > WHERE (((" & tblName & "." & tblGrp & ") = " & """" & tblGrpVal & """))"
> > rst.Open strSQL, CurrentProject.Connection, adOpenKeyset,
> > adLockOptimistic, adCmdText
> > introws = rst.RecordCount
> > varData = rst.GetRows(introws, Fields:=Array(tblValue))
> > intDim = introws - 1
> > 'Debug.Print varData(0, 3)
> > Set objXL = CreateObject("Excel.Application")
> > ReDim varMed(0 To intDim) As Double
> > For k = 0 To introws - 1
> > varMed(k) = varData(0, k)
> > Next
> > curMedian = Excel.WorksheetFunction.Median(varMed())
> > GetMedian = curMedian
> > Stopping:
> > End If
> > End Function
