Top 3

[Louise]

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,

 

[Jeff Boyce]

SELECT TOP 3 ...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

 

[Louise]

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 ...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

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,

 

[raskew via AccessMonster.com]

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

HTH - Bob

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]

 

[Jeff Boyce]

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.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

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 ...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

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,

 

[raskew via AccessMonster.com]

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

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.

Hi Jeff,

[quoted text clipped - 31 lines]