Select top n (variable) for each criteria in a table based on another table

Question

I want a VBA code to make a query to show Equip with Top ActiveTime for each ModelID (from 1st table) based on TopN for each ModelID (from the 2nd table), I know i have to use QueryDef and Sql VBA but I can't figure how to write the code

Just and Example to illustrate

My 1st table is

EquipID	Equip	ActimeTime	ModelID
1	DT1	10	1
2	DT2	6	1
3	DT3	13	1
4	DT4	15	1
5	DT5	16	2
6	DT6	12	2
7	DT7	6	2
8	DT8	13	2

My 2nd Table is

ModelID	Model	TopN
1	775	3
2	789	2

So the query result should be like (Showing the Top 3 of 775 Model and the Top 2 of 789)

Equip	ActimeTime	Model
DT4	15	775
DT3	13	775
DT1	10	775
DT5	16	789
DT8	13	789

Thanks a lot in advance, I'm really stuck at this one and solving this will help me a lot in my project

[Table1][1] [1]: https://i.sstatic.net/geMca.png [Table2][2] [2]: https://i.sstatic.net/lMPDP.png [Query Result][3] [3]: https://i.sstatic.net/cGf6k.png

Answer 1

You can do it in straight SQL - but oooh is it ugly to follow and construct

I created 4 queries with the final one resulting in what you're looking for.

The key was to get a RowID based on the sorted order you're looking for (Model and ActimeTime). You can get a pseudo Row ID using Dcount

Here's the 4 queries - I'm sure you can make one mashup if you're daring

My tables are Table3 and Table4 - you can change them in the first query to match your database. Build these queries in order as they are dependent on the one before them

qListModels

SELECT Table3.Equip, Table3.ActimeTime, Table4.Model, Table4.TopN, "" & [Model] & "-" & Format([ActimeTime],"000") AS [Model-ActTime]
FROM Table3 INNER JOIN Table4 ON Table3.ModelID = Table4.ModelID
ORDER BY Table4.Model, Table3.ActimeTime DESC;

qListModelsInOrder

SELECT qListModels.*, DCount("[Model-ActTime]","[qListModels]","[Model-ActTime]>=" & """" & [Model-ActTime] & """") AS row_id
FROM qListModels;

qListModelStartRows

SELECT qListModelsInOrder.Model, Min(qListModelsInOrder.row_id) AS MinOfrow_id
FROM qListModelsInOrder
GROUP BY qListModelsInOrder.Model;

qListTopNModels

SELECT qListModelsInOrder.Equip, qListModelsInOrder.ActimeTime, qListModelsInOrder.Model
FROM qListModelsInOrder INNER JOIN qListModelStartRows ON qListModelsInOrder.Model = qListModelStartRows.Model
WHERE ((([row_id]-[MinOfrow_id])<[TopN]))
ORDER BY qListModelsInOrder.Model, qListModelsInOrder.ActimeTime DESC;

This last one can be run anytime to get the results you want

Example Output: