Attempting to Create a "Top N Values Per Group" Query Runs Unexpec

[Guest]

Hi all,

I've modified the Method 2 solution in the following article. However, the
function is being called more times than their are records in query calling
the function. I inserted a static counter in the function. The function
runs through 32767 iterations before it encounters an Overflow error (we've
exceeded the max size of an integer. I originally thought something was
being called recursively. But that doesn't appear to be the case.

ACC2000: How to Create a "Top N Values Per Group" Query
http://support.microsoft.com/kb/210039/en-us

Here are the SQL and function being called from the query:

SELECT qry_Distribution_By_App_Offset.App_Offset,
qry_Distribution_By_App_Offset.Days_To_Sell,
qry_Distribution_By_App_Offset.[Total Apps],
qry_Distribution_By_App_Offset.[Overall Total Apps],
qry_Distribution_By_App_Offset.[Pct Sold]
FROM qry_Distribution_By_App_Offset
WHERE
qry_Distribution_By_App_Offset.Days_To_Sell<=NthInGroup([qry_Distribution_By_App_Offset].[App_Offset]/5,120)
ORDER BY qry_Distribution_By_App_Offset.App_Offset;


Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.

Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String
Dim rs As New ADODB.Recordset
Dim db As New ADODB.Connection
Dim LastNthInGroup As Integer
Static iCounter As Integer
Static LastGroupId(1 To 9, 1 To 1)

If (LastGroupId(GroupID, 1) <> 0) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastGroupId(GroupID, 1)
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "Days_To_Sell"
' Set to Group ID field name.
GroupIDName = "App_Offset"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric "", Text "'"
GDC = ""
' Set to search table.
SearchTable = "qry_Distribution_By_App_Offset"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID * 5 & GDC &
" "
SQL = SQL & "Order By [" & ItemName & "] ASC"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.

Set db = CurrentProject.Connection
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.ActiveConnection = db
rs.Open Source:=SQL, Options:=adCmdText

If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
NthInGroup = LastNthInGroup
LastGroupId(GroupID, 1) = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
NthInGroup = LastNthInGroup
LastGroupId(GroupID, 1) = LastNthInGroup
End If
End If
Debug.Print iCounter & " - " & GroupID * 5 & " - "; LastNthInGroup
iCounter = iCounter + 1
End Function

 

[Guest]

I changed the data type of iCounter to a long. The query eventually complted
after the function had been called through 309296 iterations. The final
resultset of the query contained 1080 records which is expected (120 values
for each of 9 groups). The query is based on a query which returns 1830
records.

It continues to puzzle me why the function is called 309, 296 times.

 

[Gary Walter]

I changed the data type of iCounter to a long. The query eventually
complted
after the function had been called through 309296 iterations. The final
resultset of the query contained 1080 records which is expected (120
values
for each of 9 groups). The query is based on a query which returns 1830
records.

It continues to puzzle me why the function is called 309, 296 times.

SELECT
q.App_Offset,
q.Days_To_Sell,
q.[Total Apps],
q.[Overall Total Apps],
q.[Pct Sold]
FROM
qry_Distribution_By_App_Offset as q
WHERE
q.Days_To_Sell<=NthInGroup(q.App_Offset/5,120)
ORDER BY
q.App_Offset;

first off....I don't know....

my *guess* is that when Access compiled
this query it decided to apply your filter to
all the original records in q, not the result
of q.

Try turning q into an make table query
and run above off that table to test.

Also, if App_Offset is not indexed,
then the ORDER BY may effect iterations
(or maybe even if it is indexed).

Again, all guesses...

I just know that a query based on another
query does not always mean the second query
is working with only the results of the first query.

I have to ask why the "/5 then *5"?

 

[Guest]

Gary,

Thank you for your insight. I created a table and was able to retireve the
data as expected. Taht resolved my issue. Thanks for the suggestion. The
original behavior is still peculiar, but this won't be the first where a
resolution was found with understanding th root cause.

Why the /5 and * 5? I'm working with data from different sources. I had to
do that to define the values in the correct context.

Thanks again,
Orlanzo

 

[Gary Walter]

Thank you for your insight. I created a table and was able to retireve
the
data as expected. Taht resolved my issue. Thanks for the suggestion.
The
original behavior is still peculiar, but this won't be the first where a
resolution was found with understanding th root cause.

When I said "I don't know" I was trying to keep it simple.

It is "knowable".....

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp

 

[Guest]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
I've read this in Access 2000 Developer's Handbook. It looks like the same
material was duplicated in the Access 2002 version. (Not surprising since
their both using Jet 4). While informative, it doesn't explain the behavior
I observed. I did look at the Jet Showplan output which showed something
abnormal occured when the query was compiled. Unfortunately, It isn't much
help in my case.

--- qry_Top_120_App_Points ---

- Inputs to Query -
Table 'tbl_Distribution_By_App_Offset'
Table 'tbl_Distribution_By_App_Offset'
- End inputs to Query -

01) Restrict rows of table tbl_Distribution_By_App_Offset
by scanning
testing expression ""
ERROR: unexpected empty expression
02) Sort result of '01)'

It's probably a bug somewhere in the Jet 4 engine. Since Microsoft is
technically no longer supporting Jet beyond security updates, I doubt its
something the company would be interested in investigating to say one way or
th other.

(I wonder if Microsoft will have a better set of debugging tools within the
next database engine being employed with Access 12.)

Orlanzo