Top 3

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Louise - 19 Apr 2008 10:49 GMT

Hi,

I have the below query, I have copied the SQL..

I want this query to show the Top 3 Cost Centres with the Highest Spend by
Business Unit Names. EXAMPLE FOLLOWS:

Business Unit 1 CC123 £76000
Business Unit 1 CC234 £70000
Business Unit 1 CC345 £15000
Business Unit 2 CC007 £88000
Business Unit 2 CC111 £6000
Business Unit 2 CC903 £1000
etc...

SELECT [QryImport].[Invoice Quarter], [QryImport].[Business Unit Name],
[QryImport].[Cost Centre],Sum([QryImport].[Spend]) AS [SumOfSpend]
FROM [QryImport]
GROUP BY [QryImport].[Invoice Quarter], [QryImport].[Business Unit Name],
[QryImport].[Cost Centre]
HAVING ((([QryImport Table].[Cost Centre]) Is Not Null));

Can you help me please?
Many Thanks,

Reply to this Message

Jeff Boyce - 19 Apr 2008 13:45 GMT

SELECT TOP 3 ...

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> Hi,
>
[quoted text clipped - 20 lines]
> Can you help me please?
> Many Thanks,

Reply to this Message

Louise - 19 Apr 2008 16:27 GMT

Hi Jeff,

Thanks for your reply, but where would I insert this, if I put Select Top 3
at the beginning I only get the Top 3 (i.e. 3 results), as I have 5 business
units I want to get the Top 3 for each (by Spend, so 15 results in total...

Thanks for your help,
Louise

> SELECT TOP 3 ...
>
[quoted text clipped - 22 lines]
> > Can you help me please?
> > Many Thanks,

Reply to this Message

Jeff Boyce - 19 Apr 2008 18:51 GMT

Louise

Have you looked into using the Totals query?

Perhaps another of the newsgroup readers can offer a SQL statement to do
both at once.

If you open a query in design view, right-click in the open space above the
grid, select properties, and select the Top property, Access will modify the
SQL statement accordingly.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> Hi Jeff,
>
[quoted text clipped - 31 lines]
> > > Can you help me please?
> > > Many Thanks,

Reply to this Message

raskew - 19 Apr 2008 21:27 GMT

Hi -

Couldn't get that link to open properly.

Here's an example using Northwind's Categories and Products tables.

SELECT
Categories.CategoryName
, Products.ProductName
, Products.UnitsInStock
FROM
Categories
INNER 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;

Bob

>Louise
>
[quoted text clipped - 12 lines]
>> > > Can you help me please?
>> > > Many Thanks,

Reply to this Message

raskew - 19 Apr 2008 19:02 GMT

Take a look at this MSKB article.
http://support.microsoft.com /q153/7/47.asp

HTH - Bob

>Hi Jeff,
>
[quoted text clipped - 10 lines]
>> > Can you help me please?
>> > Many Thanks,

Reply to this Message