Grouping Query

  • Thread starter Thread starter Kirk
  • Start date Start date
K

Kirk

I want to show the top ten values by group using microsoft
access, not just the top ten items from a particular
field. For example, consider a table with the fields
Account Name, Security Name, and Percent of Account. I
want to build a query that would show the top ten
securities in each account based on the Percent of Account
field. Can this be done?
 
Dear Kirk:

This is not an uncommon request. The way I'd recommend is a ranking
correlated subquery.

Probably the above tells you nothing.

Post the SQL of a query that gives you everything you want but doesn't
limit itself to the top ten you want. Next, tell me which column(s)
constitute each "group" from which you want the top ten. Finally,
specify the column(s) that rank the data, and whether you want the
largest values as top ten or the smallest values (as in, a top ten
golfer has a low score, so score would be ranked descending).

From that I can produce what you are wanting and try to explain how it
works.

I want to show the top ten values by group using microsoft
access, not just the top ten items from a particular
field. For example, consider a table with the fields
Account Name, Security Name, and Percent of Account. I
want to build a query that would show the top ten
securities in each account based on the Percent of Account
field. Can this be done?
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
Thanks for the help. Below is the sql that posts
everything. The "group" from which I want the Top Ten
is "Account_Name." The column that ranks the data is "%
_Market_Value" and I would like to display the 10 largest.

In english, I would like to see the 10 largest "Aliases"
as ranked by percent of market value for
each "Account_Name." I hope this enough explanation for
you.
Kirk




SELECT Account_Security_RF.Price_Date,
Account_Security_RF.Account_ID, Account_INF.Account_Name,
IIf(Left([cusip],4)="cash","Cash",[CUSIP]) AS Alias, Sum
([market_value]/[sumofmarket_value]) AS [%_Market_Value]
FROM (Account_INF INNER JOIN mk_totals_market_value ON
Account_INF.Account_ID =
mk_totals_market_value.Account_ID) INNER JOIN
Account_Security_RF ON Account_INF.Account_ID =
Account_Security_RF.Account_ID
GROUP BY Account_Security_RF.Price_Date,
Account_Security_RF.Account_ID, Account_INF.Account_Name,
IIf(Left([cusip],4)="cash","Cash",[CUSIP])
ORDER BY Account_Security_RF.Account_ID, Sum
([market_value]/[sumofmarket_value]) DESC;
 
Dear Kirk:

I've edited you query to make it more easily readable according to my
own personal style. I'm posting it here for my own future reference:

SELECT RF.Price_Date, RF.Account_ID, INF.Account_Name,
IIf(Left(cusip, 4) = "cash", "Cash", CUSIP AS Alias,
Sum(market_value / sumofmarket_value) AS [%_Market_Value]
FROM (Account_INF INF
INNER JOIN mk_totals_market_value TMV
ON INF.Account_ID = TMV.Account_ID)
INNER JOIN Account_Security_RF RF
ON INF.Account_ID = RF.Account_ID
GROUP BY RF.Price_Date, RF.Account_ID, INF.Account_Name, CUSIP
ORDER BY RF.Account_ID, Sum(market_value / sumofmarket_value) DESC;

It would be fine to save this query (I'll call it Kirk1) and base
further work on that. You can drop the ORDER BY clause on it as we'll
need to sort the results we base on it anyway.

SELECT Price_Date, Account_ID, Account_Name, Alias, [%_Market_Value]
FROM Kirk1 K1
WHERE (SELECT COUNT(*) FROM Kirk1 K1A
WHERE K1A.Account_ID = K1.Account_ID
AND K1A.[%_Market_Value] > K1.[%_Market_Value]) < 10
ORDER BY Account_ID, Alias DESC;

If there are ties for %_Market_Value then you could get more than 10
results. You must have a way to uniquely rank among these ties, such
as having a secondary column by which to rank (perhaps showing those
with a more recent Price_Date, for example) as a tie breaker. Of
course, this may or may not be unique even after including additional
sort columns. But this is a "real world" problem. If you asked your
secretary to give you the top ten, and there was a tie, she'd probably
come back and ask you what to do.

I had to "interpret" you spec a bit - hope I got it right. You said,
"I would like to see the 10 largest "Aliases" as ranked by percent of
market value . . ." I took it to mean you wanted to see the 10
largest % of market value. As far as I can tell, the phrase 10
largest "Aliases" is not meaningful. It doesn't appear to be a
numeric value. Hope I interpreted it right.

I don't mention that to be picky with you, but to help make you
understand on what information I based my response, especially if I
interpreted this wrongly.

So, how does this work for you?

Tom,
Thanks for the help. Below is the sql that posts
everything. The "group" from which I want the Top Ten
is "Account_Name." The column that ranks the data is "%
_Market_Value" and I would like to display the 10 largest.

In english, I would like to see the 10 largest "Aliases"
as ranked by percent of market value for
each "Account_Name." I hope this enough explanation for
you.
Kirk




SELECT Account_Security_RF.Price_Date,
Account_Security_RF.Account_ID, Account_INF.Account_Name,
IIf(Left([cusip],4)="cash","Cash",[CUSIP]) AS Alias, Sum
([market_value]/[sumofmarket_value]) AS [%_Market_Value]
FROM (Account_INF INNER JOIN mk_totals_market_value ON
Account_INF.Account_ID =
mk_totals_market_value.Account_ID) INNER JOIN
Account_Security_RF ON Account_INF.Account_ID =
Account_Security_RF.Account_ID
GROUP BY Account_Security_RF.Price_Date,
Account_Security_RF.Account_ID, Account_INF.Account_Name,
IIf(Left([cusip],4)="cash","Cash",[CUSIP])
ORDER BY Account_Security_RF.Account_ID, Sum
([market_value]/[sumofmarket_value]) DESC;
-----Original Message-----
Dear Kirk:

This is not an uncommon request. The way I'd recommend is a ranking
correlated subquery.

Probably the above tells you nothing.

Post the SQL of a query that gives you everything you want but doesn't
limit itself to the top ten you want. Next, tell me which column(s)
constitute each "group" from which you want the top ten. Finally,
specify the column(s) that rank the data, and whether you want the
largest values as top ten or the smallest values (as in, a top ten
golfer has a low score, so score would be ranked descending).

From that I can produce what you are wanting and try to explain how it
works.



Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
The query appears to work perfectly! The fact that a tie
between the %_Market_Value could occur is no big
deal...the odds of an exact tie are minute. Plus, as you
said, somebody would ask me what to do anyway.

I have one more question for you. I'm new to the database
world and have been building all of my queries in the
Design view of Access, but SQL provides much more
flexibility so that's obviously the way to go. Do you
recommend any SQL reference/"How To" books?

Thanks Again,
Kirk
-----Original Message-----
Dear Kirk:

I've edited you query to make it more easily readable according to my
own personal style. I'm posting it here for my own future reference:

SELECT RF.Price_Date, RF.Account_ID, INF.Account_Name,
IIf(Left(cusip, 4) = "cash", "Cash", CUSIP AS Alias,
Sum(market_value / sumofmarket_value) AS [% _Market_Value]
FROM (Account_INF INF
INNER JOIN mk_totals_market_value TMV
ON INF.Account_ID = TMV.Account_ID)
INNER JOIN Account_Security_RF RF
ON INF.Account_ID = RF.Account_ID
GROUP BY RF.Price_Date, RF.Account_ID, INF.Account_Name, CUSIP
ORDER BY RF.Account_ID, Sum(market_value / sumofmarket_value) DESC;

It would be fine to save this query (I'll call it Kirk1) and base
further work on that. You can drop the ORDER BY clause on it as we'll
need to sort the results we base on it anyway.

SELECT Price_Date, Account_ID, Account_Name, Alias, [% _Market_Value]
FROM Kirk1 K1
WHERE (SELECT COUNT(*) FROM Kirk1 K1A
WHERE K1A.Account_ID = K1.Account_ID
AND K1A.[%_Market_Value] > K1.[%_Market_Value]) < 10
ORDER BY Account_ID, Alias DESC;

If there are ties for %_Market_Value then you could get more than 10
results. You must have a way to uniquely rank among these ties, such
as having a secondary column by which to rank (perhaps showing those
with a more recent Price_Date, for example) as a tie breaker. Of
course, this may or may not be unique even after including additional
sort columns. But this is a "real world" problem. If you asked your
secretary to give you the top ten, and there was a tie, she'd probably
come back and ask you what to do.

I had to "interpret" you spec a bit - hope I got it right. You said,
"I would like to see the 10 largest "Aliases" as ranked by percent of
market value . . ." I took it to mean you wanted to see the 10
largest % of market value. As far as I can tell, the phrase 10
largest "Aliases" is not meaningful. It doesn't appear to be a
numeric value. Hope I interpreted it right.

I don't mention that to be picky with you, but to help make you
understand on what information I based my response, especially if I
interpreted this wrongly.

So, how does this work for you?

Tom,
Thanks for the help. Below is the sql that posts
everything. The "group" from which I want the Top Ten
is "Account_Name." The column that ranks the data is "%
_Market_Value" and I would like to display the 10 largest.

In english, I would like to see the 10 largest "Aliases"
as ranked by percent of market value for
each "Account_Name." I hope this enough explanation for
you.
Kirk




SELECT Account_Security_RF.Price_Date,
Account_Security_RF.Account_ID, Account_INF.Account_Name,
IIf(Left([cusip],4)="cash","Cash",[CUSIP]) AS Alias, Sum
([market_value]/[sumofmarket_value]) AS [%_Market_Value]
FROM (Account_INF INNER JOIN mk_totals_market_value ON
Account_INF.Account_ID =
mk_totals_market_value.Account_ID) INNER JOIN
Account_Security_RF ON Account_INF.Account_ID =
Account_Security_RF.Account_ID
GROUP BY Account_Security_RF.Price_Date,
Account_Security_RF.Account_ID, Account_INF.Account_Name,
IIf(Left([cusip],4)="cash","Cash",[CUSIP])
ORDER BY Account_Security_RF.Account_ID, Sum
([market_value]/[sumofmarket_value]) DESC;
-----Original Message-----
Dear Kirk:

This is not an uncommon request. The way I'd recommend is a ranking
correlated subquery.

Probably the above tells you nothing.

Post the SQL of a query that gives you everything you want but doesn't
limit itself to the top ten you want. Next, tell me which column(s)
constitute each "group" from which you want the top
Click to expand...
ten.
Finally,
specify the column(s) that rank the data, and whether
Click to expand...
you
want the
largest values as top ten or the smallest values (as
Click to expand...
in,
a top ten
golfer has a low score, so score would be ranked descending).

From that I can produce what you are wanting and try to explain how it
works.

On Fri, 10 Oct 2003 12:49:33 -0700, "Kirk"

I want to show the top ten values by group using microsoft
access, not just the top ten items from a particular
field. For example, consider a table with the fields
Account Name, Security Name, and Percent of Account. I
want to build a query that would show the top ten
securities in each account based on the Percent of Account
field. Can this be done?

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
 
Back
Top