Sub Query gurus, help please

[Andy959]

Using Access 2013
I have two queries that currently work as is. One returns the Top 5 equipment ID's based on some criteria (cost, labor, etc) and the other returns the Top 5 system ID's based on the same criteria.

I'm trying to create a 3rd query that will return the Top 2 equipment for each system from the Top 5 query. This should yield 10 results. I think I should be adding the subquery to the Equipment field "criteria" but I'm having trouble coming up with something that doesn't return only 2 results or significantly more than 10.

Each system has hundreds of records. The examples I posted just showed an example of what the results from my two queries were. In reality there are dozens of systems and hundreds of equipment per each system. When ordering the equipment by Top 5 for cost, multiple systems will be left off that list, which is how it should be.

Examples:

Query 1 returns:
Equipment Cost System
1..............10......A
2..............9........E
3..............8........E
4..............7........B
5..............6........E

Query 2 returns:
System Cost
E.........100
B.........90
A.........80
D.........70
C.........60

The result that I would like to see for the third query is
System Equipment Cost
E..............2...........9
E..............3...........8
B..............4...........7
B..............#...........#
A..............#...........#
D..............#...........#
D..............#...........#
C..............#...........#
C..............#...........#
(Note these #'s would actually be filled in, I just didn't feel like typing that many examples.)

Equipment query:

SELECT TOP 5 Equipment.[Work Center], [SAP-Equipment].[Building # Adjusted Description] AS Description, Equipment.Equipment, Equipment.Cost, Equipment.PM01, Equipment.PM02, [PM02]/[PM01] AS [PM Ratio]FROM Equipment INNER JOIN [SAP-Equipment] ON Equipment.Equipment = [SAP-Equipment].Equipment
WHERE (((Equipment.[Work Center])=[Forms]![DisplayForm].[GetWC]))
ORDER BY Equipment.Cost DESC , Equipment.Labor DESC , Equipment.ID DESC;

System query:

SELECT TOP 5 System.[Work Center], [SAP-System].Description, System.System, System.Cost, System.PM01, System.PM02, [System]![PM02]/[System]![PM01] AS [PM Ratio]
FROM System INNER JOIN [SAP-System] ON System.System = [SAP-System].[Functional Loc]
WHERE (((System.[Work Center])=[Forms]![DisplayForm].[GetWC]))
ORDER BY System.Cost DESC , System.Labor DESC , System.[Total Work Orders] DESC , System.ID DESC;

Any thoughts?
Thanks in advance!

 

[Andy959]

Figured it out...but it raised a new question for another thread. If anyone else searches, here's the code:

SELECT [SysTop5 Cost].System, [SAP-Equipment].[Building # Adjusted Description] AS Description, EquipWSys.Equipment, EquipWSys.Cost, EquipWSys.PM01, EquipWSys.PM02, [EquipWSys]![PM02]/[EquipWSys]![PM01] AS [PM Ratio]FROM ([SysTop5 Cost] INNER JOIN EquipWSys ON [SysTop5 Cost].System = EquipWSys.System) INNER JOIN [SAP-Equipment] ON EquipWSys.Equipment = [SAP-Equipment].Equipment
WHERE (((EquipWSys.Equipment) In (SELECT TOP 2 Equipment                            
   FROM EquipWSys AS Dupe                              
   WHERE Dupe.System = EquipWSys.System
   ORDER BY Dupe.Cost DESC, Dupe.Labor DESC, Dupe.ID DESC)))
ORDER BY [SysTop5 Cost].System, EquipWSys.Cost DESC;