Q: a query

[Guest]

Hello,
I have myTtable and I have ClassID field, now I want to bring myTtable.* for
at most 10 students for each ClassID. There might be more/less than 10 per
ClassID, if there is 10 different ClassID in the database I should be seeing
at most 100 records. How can I write this query?
Thanks,
Jim.

 

[Guest]

Assuming you have a table for classes:

SELECT myClass.ClassID, myTtable.*
FROM myClass LEFT OUTER JOIN MyTtable ON MyTtable.ClassID = myClass.ClassID

Change: MyClass for the name of your class table

If you don't have a table for classes:

SELECT myTtable.* FROM MyTtable Group by ClassID

Mauricio Silva

 

[Guest]

Hi Mauricio,

Thanks for the reply.

SELECT myTtable.* FROM MyTtable Group by ClassID

This is the right option and this groups correctly, now my question how can
I get only first 10 records of each group? I do not want to see all the
records, only first 10.
Thanks,
Jim.

 

[Danny J. Lesandrini]

I've thought about this question for an issue I had once, and I could only
figure out how to do it in VBA code. Here's my solution:

Public Function GetTop10CampaignWaves()
On Error Resume Next

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim sql As String
Dim lID As Long
Dim sOutSQL As String

Set dbs = CurrentDb
sql = "SELECT DISTINCT TOP 10 CampaignID FROM tblWave " & _
"ORDER BY CampaignID DESC"
Set rst = dbs.OpenRecordset(sql, dbOpenSnapshot)

Do Until rst.EOF
lID = Nz(rst!CampaignID)
If Len(sOutSQL) > 0 Then sOutSQL = sOutSQL & " UNION "

sOutSQL = sOutSQL & "SELECT TOP 10 CampaignID, PromoCode " & _
"FROM tblWave WHERE CampaignID=" & lID & vbCrLf

rst.MoveNext
Loop

Set qdf = dbs.QueryDefs("qryTopTenPromoCodes")
qdf.sql = sOutSQL
Debug.Print sOutSQL
DoCmd.OpenQuery "qryTopTenPromoCodes"

Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

End Function



Resulting SQL:
SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=98
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=97
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=96
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=95
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=94
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=93
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=92
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=91
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=90
UNION SELECT TOP 10 CampaignID, PromoCode FROM tblWave WHERE CampaignID=89

 

[Guest]

Try:
SELECT TOP 10 myTtable.* FROM MyTtable Group by ClassID

Mauricio Silva