Top 25 in a filtered range

[ali32j]

Hi all

Can anyone help me on an extension of a previous question, I am using the following code

SELECT BusinessWarehouse.[Ship To], BusinessWarehouse.Territory, BusinessWarehouse.Delta, BusinessWarehouse.[Period Yr-1], BusinessWarehouse.[Period T/O], BusinessWarehouse.[FY-1 Full Year], BusinessWarehouse.[FY-2 Full Year]
FROM BusinessWarehouse
WHERE (((BusinessWarehouse.Territory)=[forms]![frmsalesreportbyterritory]![territory]) AND ((BusinessWarehouse.Delta) In (select TOP 25 delta
from businesswarehouse
order by delta desc)))
ORDER BY BusinessWarehouse.Delta DESC;

what it needs to return is top 25 in a territory, but seems to take top 25 from the from all territories then filters within this list for the territory selected, hence it is only showing a few of the overall 25... can anyone offer some guidance?

Ali

 

[lameid]

How about...

SELECT BusinessWarehouse.[Ship To], BusinessWarehouse.Territory, 
BusinessWarehouse.Delta, BusinessWarehouse.[Period Yr-1], BusinessWarehouse.[Period T/O], 
BusinessWarehouse.[FY-1 Full Year], 
BusinessWarehouse.[FY-2 Full Year]
FROM BusinessWarehouse
WHERE BusinessWarehouse.Delta
In (select TOP 25 delta
    From businesswarehouse
    Where BusinessWarehouse.Territory=[forms]![frmsalesreportbyterritory]![territory]
     Order by delta desc)
ORDER BY BusinessWarehouse.Delta DESC;

You needed the territory criteria in the subquery not the main query.

 

[ali32j]

hi lameid

thanks worked great

Ali