Recordset problem

[jmersing]

I'm using this code to create multiple SQL statements that select the top ten invoice numbers by the user_id. It appears to produce the correct SQL but disregards the Top 10 in the query results and lists each user even if it is more than ten times.

I would expect to see each user listed <= 10 times with the appropriate invoices. Can anyone see what I'm missing? Thanks

I'm using this SQL in the query that calls the function:

SELECT DUR_March.invoice_nbr, DUR_March.user_id
FROM DUR_March
WHERE (((DUR_March.user_id)>=NthInGroup([DUR_March].[user_id],10))

Function NthInGroup(GroupID, N)
Static LastGroupId, LastNthInGroup
Dim ItemName, GDC, GroupIDName, SearchTable
Dim SQL As String, rs As DAO.Recordset, db As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef

If (LastGroupId = GroupID) Then
NthInGroup = LastNthInGroup
Else
ItemName = "invoice_nbr"
GroupIDName = "user_id"

SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & ""
SQL = SQL & "Order By [" & ItemName & "] Desc"

Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then

LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup

Else
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If
End Function

This is the SQL produced in VBA: (Which is correct)

Select Top 10 [invoice_nbr] From [DUR_March] Where [user_id]='ABCS6E'Order By [invoice_nbr] Desc
Select Top 10 [invoice_nbr] From [DUR_March] Where [user_id]='ABCPA9'Order By [invoice_nbr] Desc
Select Top 10 [invoice_nbr] From [DUR_March] Where [user_id]='ABC6SL'Order By [invoice_nbr] Desc

 

[Fazza]

Hi, Just guessing: what about adding a space before the 'ORDER BY'? Fazza

 

[Will_B]

I think the error lies in the SQL that makes the call to your function. In that SQL, the WHERE clause is:

WHERE (((DUR_March.user_id)>=NthInGroup([DUR_March].[user_id],10))

... which means you are telling the SQL to pull not only the people responsible for the Top N, but anyone else whose ID number is GREATER than the "Top" people. For example, if your Top N people have UserIDs of A1, B1, and C1, I think your SQL will also pull results for people with User_ID D1, E1, F1, etc. --- anything "higher" than UserID C1. Are you seeing this situation in your results?

I think if you eliminate the ">" in the WHERE statement, your problem is fixed.

 

[jmersing]

Its getting even more bizarre now, both of your suggestions seemed plausible but if I add a space as suggested, no impact.

If I change >= to = or even <= it goes into an infinite loop that I can see executing in the immediate window. I think it's so close I just can't see the problem. Here is exactly what the SQL looks like in the immediate window:

Select Top 10 [invoice_nbr] From [DUR_March] Where [user_id]='ABCRI4 'Order By [invoice_nbr] Desc

Notice the trailing spaces in the user_id can't seem to strip them out.

But if I copy 3 of the SQL rows generated and use a "Union" query, I get the desired results i.e. 10 results per User ID.

Ugh!!!

 

[Fazza]

Is the space in the wrong place?

Where [user_id]='ABCRI4 'Order By

Should be

Where [user_id]='ABCRI4' Order By

 

[SydneyGeek]

I'm not sure what the GDC is meant to do in the code; it's declared but not given a value.
Try changing this line...

SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & ""

to this

SQL = SQL & "Where [" & GroupIDName & "]='" & GroupID & "'"

Denis

 

[jmersing]

I commented my line out and used Sydney's, same result.... here is all of the SQL



SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
'''SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & groupID & GDC & ""
SQL = SQL & "Where [" & GroupIDName & "]='" & groupID & "'"
SQL = SQL & "Order By [" & ItemName & "] Desc"

 

[jmersing]

I'd also be open to any other methods.The task is somewhat simple, all I'm trying to do is return ten records for each user in the table, some have 50 entries, some have three, I would just like to return 10 or less than 10 if that is all there is.

I'm surprised that this is so difficult. Is there anything else I can post to be more clear?

Thanks for all of your input.

 

[SydneyGeek]

The concept isn't the problem so much as the syntax. Often you just have to tweak, and it's hard to do that from a distance.

It looks like you used Method 2 from this post...
http://support.microsoft.com/kb/210039

Have you tried the first option?

It's also possible to create a report that restricts you to N records per group

Denis