Top N Per Group (1 Viewer)

[jazi]

I know you have probably seen this a trillion times, but being new to all this, it is discouraging to say the least! Please help...

I think maybe I should upload the database, let me know.

Here are the names:

Table name: PPM Data

Fields:
Company
PPM Month
PPM Amount

There are 10 different companies, and I need top 6 months data per each Company, to eventually only show the 6 amounts. I have tried all the equations I can find. But I don't think I am doing it right. I have tried to make 10 different queries for each company and than tried to join those queries, and that just didn't work.

I have tried Allen Browne, confusing, and I have tried Acc2002 for their equations for Top N per group, I still can't make out what they are saying without the actual database to look at.

 

[EMP]

Please attach your database, preferably in Access 2000 format.

^

 

[gemma-the-husky]

i've just tried this, and its not easy is

i thought groupby, top5 would work, bit it just shows the top 5 of the lot, not of each group

not sure without a lot of delving - thers no apparently obvious way is there.

what help index reference where you looking at

 

[raskew]

Hi -

Here's a example using the Categories and Products tables in Northwind. Try copying/pasting to a new query in Northwind, then switch to design view to see how it's constructed.

SELECT Categories.CategoryName AS Expr1, Products.ProductName, Products.UnitsInStock
FROM Categories LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.UnitsInStock) In (SELECT
    Top 3 [UnitsInStock] 
FROM
   Products 
WHERE
   [CategoryID]=[Categories].[CategoryID] 
ORDER BY
   [UnitsInStock] Desc)))
ORDER BY Categories.CategoryName, Products.UnitsInStock DESC;

Also, take a look at this MSKB article:
http://support.microsoft.com /q153/7/47.asp
HTH - Bob

 

[jazi]

This is my SQL

Getting an error


SELECT [PPM Data].ID, [PPM Data].Company, [PPM Data].[PPM Month], [PPM Data].[Grant Shipped], [PPM Data].[Grant Rejected], IIf([Grant Shipped]=0,0,[Grant Rejected]/[Grant Shipped])*1000000 AS [Monthly Grant PPM], [PPM Data].[Hart Shipped], [PPM Data].[Hart Rejected], IIf([Hart Shipped]=0,0,[Hart Rejected]/[Hart Shipped])*1000000 AS [Monthly Hart PPM],
WHERE ([PPM Data].[PPM Month]) In (SELECT Top 3[PPM Data].[PPM Month])
FROM [PPM Data]
GROUP BY [PPM Data].ID, [PPM Data].Company, [PPM Data].[PPM Month], [PPM Data].[Grant Shipped], [PPM Data].[Grant Rejected], IIf([Grant Shipped]=0,0,[Grant Rejected]/[Grant Shipped])*1000000, [PPM Data].[Hart Shipped], [PPM Data].[Hart Rejected], IIf([Hart Shipped]=0,0,[Hart Rejected]/[Hart Shipped])*1000000
ORDER BY [PPM Data].Company, [PPM Data].[PPM Month] DESC;

 

[Jon K]

See if this is the query that you wanted.

SELECT ID, Company, Month, [PPM Month], [Grant Shipped], [Grant Rejected],
Round(IIf([Grant Shipped]=0,0,[Grant Rejected]/[Grant Shipped])*1000000,4) AS [Monthly Grant PPM],
[Hart Shipped], [Hart Rejected],
Round(IIf([Hart Shipped]=0,0,[Hart Rejected]/[Hart Shipped])*1000000,4) AS [Monthly Hart PPM]
FROM [PPM Data]
WHERE ID In (Select top 6 ID from [PPM Data] as s where s.[Company]=[PPM Data].Company order by s.[PPM Month] desc)
ORDER BY Company, [PPM Month]

I've rounded the two calculations to 4 places of decimal.


Note:
Using a subquery in a correlated way is inefficient. If the table is large, you may consider using VBA code to loop through a Company list and update a temporary table.

Alternatively, if monthly data are inputted in the table for every company, you can use a parameter query to supply a starting date when the query is run. This can run much faster than a correlated subquery.

SELECT ID, Company, Month, [PPM Month], [Grant Shipped], [Grant Rejected], Round(IIf([Grant Shipped]=0,0,[Grant Rejected]/[Grant Shipped])*1000000,4) AS [Monthly Grant PPM], [Hart Shipped], [Hart Rejected], Round(IIf([Hart Shipped]=0,0,[Hart Rejected]/[Hart Shipped])*1000000,4) AS [Monthly Hart PPM]
FROM [PPM Data]
WHERE [PPM Month] >=[Enter starting date]
ORDER BY Company, [PPM Month]

.

 

[jazi]

Thanks!

Thank you. That worked beautifully.