SELECT TOP 5 from INNER JOIN?

[CTOROCK]

I have two querries and I would like to return the Top 5 inner joins that it finds. i.e I have an item in one query and it is found many times in another. I would like it to report the top 5 CustNames with the 5 biggest quantities. I try this:

SELECT qryPollyEnduranceItems.ITEM, qryInvoices.ICustName, qryInvoices.IShipQty
FROM qryPollyEnduranceItems
INNER JOIN qryInvoices ON qryPollyEnduranceItems.ITEM = qryInvoices.IItemNo
WHERE qryInvoices.ICustName = (SELECT TOP 5 ICustName FROM qryInvoices)
ORDER BY qryPollyEnduranceItems.ITEM, qryInvoices.IShipQty DESC;


but I get "At most one record can be returned by this subquery" Error.

Any idea? Thanks in advance for your help


The greatest risk, is not taking one.

 

[dhookom]

At first glance, I would suggest you change this to

SELECT qryPollyEnduranceItems.ITEM, qryInvoices.ICustName, qryInvoices.IShipQty
FROM qryPollyEnduranceItems 
INNER JOIN qryInvoices ON qryPollyEnduranceItems.ITEM = qryInvoices.IItemNo
WHERE qryInvoices.ICustName [b][red]IN[/red][/b] (SELECT TOP 5 ICustName FROM qryInvoices)
ORDER BY qryPollyEnduranceItems.ITEM, qryInvoices.IShipQty DESC;

Duane
Hook'D on Access
MS Access MVP

 

[CTOROCK]

I've tried that and I get result, but it's not the result I'm looking for. I'm trying to return the 5 top customer names with the 5 largest quantities. I'm trying to hammer it out. Thanks in advance to any suggestions.

The greatest risk, is not taking one.

 

[Golom]

Try

SELECT TOP 5 QP.ITEM, QI.ICustName, QI.IShipQty

FROM qryPollyEnduranceItems As QP INNER JOIN qryInvoices As QI 
     ON QP.ITEM = QI.IItemNo

ORDER BY QI.IShipQty DESC;

Your query will return the TOP 5 customers in whatever order they are returned from the database because there is no ORDER BY in the sub-query.

 

[CTOROCK]

Thanks for the reply Golom. This gives me the top 5 records of the query. I was trying to get the top 5 matches for each record from the other table. The 1st table has a list of items and the second has customer invoice totals. I wanted to return the top 5 customers for my selected items (table 1). So I was trying things out like:

SELECT *, (SELECT TOP 5 (SumOfIShipQty) FROM qryInvoices WHERE qryInvoices.IItemNo = qryPollyEnduranceItems.ITEM)
FROM qryPollyEnduranceItems
ORDER BY qryInvoices.SumOfIShipQty DESC;


but can't get it to work. Thanks again.

The greatest risk, is not taking one.

 

[r937]

SELECT qryPollyEnduranceItems.ITEM
     , T.ICustName
     , T.IShipQty
  FROM qryPollyEnduranceItems 
INNER 
  JOIN qryInvoices AS T
    ON T.IItemNo = qryPollyEnduranceItems.ITEM 
 WHERE ( SELECT COUNT(*)
           FROM qryInvoices
          WHERE IItemNo = T.IItemNo
            AND IShipQty > T.IShipQty ) < 5

r937.com | rudy.ca