Grouped Data

[Mohammad Eassa]

Hello ,
I want to group number of N data , for example
1st 20 records asigned by N-1
2nd 20 Records assigned By N-2
and sooo

I tried Dcount but its very slow
I have 50000 records on table

 

[Ranman256]

'the code below uses a query qsTop20Recs of Top 20 recs where [Sent] = null
'then cycles thru the query, getting one 20 time block at a time, filling in the Sent field.

Public Sub MakeTopBlocks()
Dim vStartDate, vEndDate
Dim lBlock As Long, lCnt As Long
Dim sSql As String
Const kQRY = "qsTop20Recs"    'this query gets 20 recs, with null [Sent]
   
lBlock = 1
bMore = True
lCnt = DCount("*", kQRY)

While lCnt > 0
    sSql = "UPDATE qsTop20Recs SET qsTop20Recs.Sent = " & lBlock & " WHERE ((qsTop20Recs.Sent) Is Null);"
    DoCmd.RunSQL sSql
    
    lBlock = lBlock + 1
    lCnt = DCount("*", kQRY)
Wend

MsgBox "Done"
End Sub

 

[arnelgp]

you might try Partition on your Query:

SELECT Partition([ID], Dcount("*", "YourTable"), 1, 20) As Group, Count("*") As Count FROM YourTable GROUP BY Partition([ID], Dcount("*", "YourTable"), 1, 20)