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,

 

[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,