Aggregate rank query question

[Mikael Lindqvist]

Hi everyone,

I found an older thread that partly answered my question:
http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

Which describes how to create a query that ranks different fields.

Now, I have a table with various records for same field and I need to sum
the records (Profits) and then return the (companys) with biggest aggregate
Profit.

I believe I need to change this query:

(SELECT Count(*) FROM tblCompanyProfits AS VT
WHERE VT.Region = tblCompanyProfits.Region AND
VT.Profits > tblCompanyProfits.Profits)+1

To an aggregate query, but what should it look like?

If I get above right I guess the rank-part does work as given?

GroupRank: (SELECT Count(*) FROM tblCompanyProfits AS VT WHERE
VT.Region = tblCompanyProfits.Region AND VT.Profits >
tblCompanyProfits.Profits)+1

Good weekend to everyone!

Cheers,
Mikael

 

[Jeanette Cunningham]

It's easier for us volunteers if you try the queries yourself. I assume you
know how to change the field and table names in the example and replace with
yours.
If there is a problem, post back with a description of the error or what
went wrong.

Jeanette Cunningham

 

[Mikael Lindqvist]

What makes you think I haven't tried the query? :>

Anyhow, it works really fine, as is, but unless I find a way to make it sum
(aggregate) I have to create a new table with "sum profits" and run below
query..

OR if someone knows how to modify this query to calclulate the profits and
then do the rank it would save me a second query (and temporary table).

Kindly,
Mikael

 

[Jeanette Cunningham]

Mikael,
this post remained unanswered and as I found it unclear, I assumed others
did as well.
I had a look at the older thread and I am even more unclear.
Perhaps it would be better if you could post with a few rows of the data you
want to aggregate and rank,
and a few rows of what you want it to look like.

Jeanette Cunningham

 

[Mikael Lindqvist]

Hi Jeanette,

Thanks for your prompt reply. Ok, here's an example:

1 table and 4 fields (Company, Country, Profit, Period)

I want a query that RANK top 3 Companys in each Country for each given
period (period is given in format "yyyymmdd").

In order to do this the query needs to SUM profits for a given month
(yyyymm) and then return the top 3 companys in each period AND country.

Example query output (assuming 1 country and 1 period)

IKEA, Sweden, 2000USD, 2007011, "Rank 1"
Volvo, Sweden, 10000USD, 200711 "Rank 2"
Saab, Sweden, 800 USD, 200711, "Rank 3"

I hope this makes sense, otherwhise I'll make an even bigger effort.

Many thanks in advance!

//Mikael

 

[Jeanette Cunningham]

Mikael,
I tried this query with some data I invented.

SELECT tblCompanyProfits.Period, tblCompanyProfits.Country,
tblCompanyProfits.Company, tblCompanyProfits.Profits, (SELECT Count(*) FROM
tblCompanyProfits AS VT WHERE
VT.Country = tblCompanyProfits.Country AND VT.Period =
tblCompanyProfits.Period AND VT.Profits >
tblCompanyProfits.Profits)+1 AS GroupRank
FROM tblCompanyProfits
WHERE ((((SELECT Count(*) FROM tblCompanyProfits AS VT
WHERE VT.Country = tblCompanyProfits.Country AND VT.Period =
tblCompanyProfits.Period AND
VT.Profits > tblCompanyProfits.Profits)+1)<4))
ORDER BY tblCompanyProfits.Period DESC , tblCompanyProfits.Country,
tblCompanyProfits.Profits DESC;

It returned

Period Country Company Profits GroupRank
1/03/2007 Sweden kkk 92 1
1/03/2007 Sweden JPP 65 2
1/03/2007 Sweden Blueray 50 3
1/03/2007 UK yyy 95 1
1/03/2007 UK xxx 50 2
1/03/2007 UK Dadrosa 47 3
1/01/2007 Sweden zzz 106 1
1/01/2007 Sweden Erruivxe 98 2
1/01/2007 Sweden kkk 72 3
1/01/2007 UK Dadrosa 87 1
1/01/2007 UK yyy 65 2
1/01/2007 UK xxx 60 3

I just left the dates as general date format, you can change this as you
like it.
Note the Group rank column just has the numbers - they come from using the
Count function.
I need to do some more work on it to handle ties.
Hope this helps.

Jeanette Cunningham

 

[Mikael Lindqvist]

Hi,

Unfortunately this does exactly the same thing as the original query that I
want to modify.

Your query selects and returns a RANK "as is"... that is, it does not sum
(aggregate).

For example, with your sample data I would like to have a rank for january
2007, and since there are multipe companies in this list I need to sum their
profits, hence:

Period Country Company Profits GroupRank
1/03/2007 Sweden kkk 92 1
1/03/2007 Sweden JPP 65 2
1/03/2007 Sweden Blueray 50 3
1/01/2007 Sweden zzz 106 1
1/01/2007 Sweden Erruivxe 98 2
1/01/2007 Sweden kkk 72 3

Should be (show only 2 largest companies):

200701 Sweden kkk 164 1
200701 Sweden zzz 106 2

Where "kkk" is sum of 72+92.

Maybe this change the picture somewhat?

Kindly,
Mikael
Sweden

 

[Jeanette Cunningham]

Mikael,
This does it in 2 queries - as far as I can tell, you need to use 2 queries.
1st query
--------------------------
SELECT tblCompanyProfits.Period, Sum(tblCompanyProfits.Profits) AS
TotProfits, tblCompanyProfits.Country, tblCompanyProfits.Company
FROM tblCompanyProfits
GROUP BY tblCompanyProfits.Period, tblCompanyProfits.Country,
tblCompanyProfits.Company
ORDER BY tblCompanyProfits.Period;
--------------------------
save the above query as Q

2nd query
--------------------------
SELECT Q.Period, Q.Country, Q.Company, Q.TotProfits, (SELECT Count(*) FROM Q
AS VT WHERE
VT.Country = Q.Country AND VT.Period = Q.Period AND VT.TotProfits >
Q.TotProfits)+1 AS GroupRank
FROM Q
WHERE ((((SELECT Count(*) FROM Q AS VT
WHERE VT.Country = Q.Country AND VT.Period = Q.Period AND
VT.TotProfits > Q.TotProfits)+1)<4))
ORDER BY Q.Period DESC , Q.Country, Q.TotProfits DESC;

 

[Mikael Lindqvist]

Yes this works great :>

But maybe I could combine them in a sub-query? I'll give it a go.

Have a good day!

Kindly,
Mikael