2

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.stack.imgur.com/geMca.png [Table2][2] [2]: https://i.stack.imgur.com/lMPDP.png [Query Result][3] [3]: https://i.stack.imgur.com/cGf6k.png

Ken White
  • 123,280
  • 14
  • 225
  • 444
MMN
  • 23
  • 4
  • You don't need VBA to make a query - write the query first then show us the SQL and the problem you encounter if any – dbmitch Apr 06 '22 at 03:16
  • 1
    You _do_ need VBA to create the query. While `Model` can be retrieved and filtered on, the `TOP n` clause cannot be dynamic. Thus, use VBA to modify queries to hold - in this case - either `TOP 2` or `TOP 3`. – Gustav Apr 06 '22 at 09:11
  • @dbmitch I tried to write the query in SQL but TOP argument don't accept a variable it have to be a number (i.e. Select TOP 5 ActiveTime), maybe you can help me with an example to how to do it in SQL – MMN Apr 06 '22 at 11:41
  • @Gustav The above tables were just an example, unfortunately the Model table has much more entries and we need to show for each Model the Top Equip achieved Active time based on the adjacent Top N – MMN Apr 06 '22 at 11:47
  • OK, go ahead. Have in mind please, that SO is not a code writing service but a code _helper_ forum. – Gustav Apr 06 '22 at 11:55
  • 1
    @Gustav I was able to do it with just SQL but I guess Dcount is technically VBA. Half right? – dbmitch Apr 07 '22 at 01:19
  • 1
    Yes. If you like, `DCount` can be replaced with a subquery finding the count. It may be a bit faster but not slower. – Gustav Apr 07 '22 at 06:17

1 Answers1

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:

Results of Top N Model Query

dbmitch
  • 5,361
  • 4
  • 24
  • 38
  • 1
    Very neat. If you have many records, and the RowId appears to be the bottleneck, you might take advantage of my `RowNumber` function found at _GitHub_: [VBA.RowNumbers](https://github.com/GustavBrock/VBA.RowNumbers). – Gustav Apr 07 '22 at 06:22
  • @ dbmitch Just brilliant smart solution, I totally believed that it can be done only by VBA, thanks a lot mate, not just for the help but also I really learnt a lot from your solution – MMN Apr 07 '22 at 10:46
  • @Gustav Thanks mate for your time and guidance, The RowNumber function looks very interesting too – MMN Apr 07 '22 at 11:08
  • @MMN thanks - glad it helped. If you consider it a solution, please mark it as the answer with that checkmark - it's our little reward for helping. – dbmitch Apr 07 '22 at 16:46