Sub Query help

[Andy83]

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.

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!

[June7]

How could you get 10 results from the Top 5 equipment query? C and D have no records in the Top 5 equipment and A and B have only one record each. Could try joining these two queries on System and the result should be 7 records so there is your Top 10.

Review this for nested query to get Top N records per group http://allenbrowne.com/subquery-01.html#TopN

[Andy83]

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. I probably should have specified that part.

The third query I'm trying to come up with should take the Top 5 systems, and find the Top 2 equipment associated with each system.

That nested link looks like what I need, just a much simpler version. I'll see if I can implement that. Thank you.