tricky access query 3

[firegambler]

Hi Folks,

Would be great if somebody could give me a hint on this.

I have a table that roughly looks like this:

Profitcenter Sales Quantity Net Sales
100 30 465
100 10 470,12
100 12 47,12
100 303 447,12
100 304 564
100 210 81,1
300 546 546
300 131 746
300 8413 3214
300 1321 1002,5
300 11 15,21
500 8 9,5
500 3 4,2
605 1 2,1
605 78 144,2
605 96 96,51
605 56 55,54
605 45 56,21
605 32 341
605 45 451,2

Actually my table is about 3000 lines long...
Now I would like to have a query that shows the five data records with the highest sales quantity per profitcenter
(sorted in a descending order)
so that I get the topmost 5 for profitcenter 100, the topmost 5 for profitcenter 300, etc.

I managed to have get them in seperate queries but I'd like to have the results in one query.

Any suggestions would be very appreciated.

Thanks in advance!!




regards

 

[mp9]

For one profit center:

SELECT TOP 5
Profitcenter, [Sales Quantity], [Net Sales]
FROM YourTableName
WHERE Profitcenter = 100
ORDER BY [Sales Quantity] DESC;

Then you could union them together - UNTESTED EXAMPLE:

SELECT TOP 5
Profitcenter, [Sales Quantity], [Net Sales]
FROM YourTableName
WHERE Profitcenter = 100
UNION SELECT TOP 5
Profitcenter, [Sales Quantity], [Net Sales]
FROM YourTableName
WHERE Profitcenter = 300
ORDER BY [Sales Quantity] DESC;

http://www22.brinkster.com/accessory/

 

[firegambler]

Hi,

Thanks for your quick reply!
Actually I unfortunately don't know how to handle SQL and implement SQL scripts...
Is there a way to do this without using sql?

regards

 

[SkipVought]

Hi,

In your query grid editor there sould be an SQL Tool button or a DESIGN button that has an SQL option.

This opens the SQL Editor.

Just copy 'n' paste the posted code into the SQL editor and RUN.

Sorry, the graphical editor cannot handle complex SQL statements.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[firegambler]

Hi guys,

it took me quite a while to get into that stuff
since i had a few linked queries and tables but finally
it worked out.
thanks a lot.
this will save me about 40 minutes a day from now on

regards

 

[combo]

In case of significant or variable number of Profitcenters there may be a problem with query size or rewriting union query to include all profitcenters.
An alternative way (here table name is just 'Table', uses self-joined table), SQL string that can be edited in visual design view:

SELECT Table.Profitcenter, Table.[Sales Quantity], Table.[Net Sales], 1+Sum(IIf([table_1].[Sales Quantity]>

.[Sales Quantity],1,0)) AS RANK
FROM

INNER JOIN

AS Table_1 ON Table.Profitcenter = Table_1.Profitcenter
GROUP BY Table.Profitcenter, Table.[Sales Quantity], Table.[Net Sales]
HAVING (((1+Sum(IIf([table_1].[Sales Quantity]>

.[Sales Quantity],1,0)))<=5))
ORDER BY Table.Profitcenter, Table.[Sales Quantity] DESC;

Access can separate RANK definition and condition (<=5) into two columns, if so, the condition can be copied to RANK column and the last column deleted.
In this query, in case of more than one record with fifth result, all records with this sales quantity are listed.

combo