Top Values

[akaivyleaf]

I have a database that keeps extensive data on response times, by date. I want to get the top 14 people who responded to EACH incident on a selected date. (There could be instances of 7 or 10 incidents per date and 14+ people responding to each incident)

I believe that some incantation of Top Value will produce what I want but right now, it just gives me the top 14 people per date. Not per incident, per date. How do I get it to look at each incident (listed by incident number) and then the top 14 by this number, by date. Does this make sense or do I need to explain it diffently.

 

[Remou]

Hi
I think this is similar: Top ten list thread701-1081059

 

[foto66]

akaivleaf.

the fundamental problem is the GROUP/ORDER functions of SQL do not allow nesting of the TOP function. i have tried dozens of things and there is really no solution apart from a VBA loop and a query call for each incident. if you can post your table/field names, i could possibly help you out. here is a stab that may help you out a bit:

dim db as database, wsJet as workspace
dim SQ as string
dim rs1 as recordset, rs2 as recordset

set wsJet = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
set db = wsJet.OpenDatabase("C:\myDatabase.mdb")

SQ = "SELECT incident_ID FROM table1 WHERE " & _
"date=#01/jun/2005#;"

set rs1=db.OpenRecordset(SQ, dbOpenSnapshot)

do until rs1.eof
SQ = "SELECT TOP 14 person FROM table1 WHERE " & _
"incident_ID=" & rs.fields!incident_ID & _
"ORDER BY personscore DESC;"
set rs2 = db.OpenRecordset(SQ, dbOpenSnapshot)

[here you decide what you want to do with the rs2 data]

rs1.movenext
loop

 

[akaivyleaf]

Thanks for this bit of code... let me play with what you've offered up and see where I come in. I'll post more as I progress.